Untitled

-- 17. 
-- แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด
SELECT customers.name AS customer_name,
       products.name AS product_name,
       order_items.amount,
       order_items.price,
       (order_items.amount * order_items.price * (1 - order_items.discount)) AS total_purchase
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id
ORDER BY customer_name, total_purchase DESC;
  
-- 18)
-- แสดงชื่อลูกค้าที่มียอดสั่งไฮยีน่ามากสุด 5 อันดับแรก
SELECT customers.name AS customer_name,
       SUM(order_items.amount * order_items.price * (1 - order_items.discount)) AS total_purchase
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
GROUP BY customers.id, customers.name
ORDER BY total_purchase DESC
LIMIT 5;

-- 19. 
--จงหาชื่อ customer ที่มียอดสั่งซื้อมากกว่า 10000
SELECT customers.name AS customer_name,
       SUM(order_items.amount * order_items.price * (1 - order_items.discount)) AS total_purchase
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
GROUP BY customers.id, customers.name
HAVING total_purchase > 10000;

-- 20. 
-- แสดงชื่อเซลล์และยอดขาย เฉพาะคนที่ยอดขายมากกว่าค่าเฉลี่ย (ยอดรวม / ตามจำนวน sales (ฝ่ายขาย))
SELECT employees.name AS salesperson_name,
       SUM(order_items.amount * order_items.price * (1 - order_items.discount)) AS total_sales
FROM employees
JOIN orders ON employees.id = orders.employee_id
JOIN order_items ON orders.id = order_items.order_id
GROUP BY employees.id, employees.name
HAVING total_sales > 
    (SELECT AVG(subtotal) 
    FROM (SELECT SUM(order_items.amount * order_items.price * (1 - order_items.discount)) 
    AS subtotal 
    FROM order_items 
    GROUP BY order_items.order_id) AS subquery);

-- 21. 
-- แสดงชื่อ employee ที่ไม่มียอดขาย และแสดงลูกค้าที่ไม่มียอดซื้อ (2 results)
-- 21.1 แสดงชื่อ employee ที่ไม่มียอดขาย
SELECT employees.id AS employee_id, employees.name AS employee_name
FROM employees
LEFT JOIN orders ON employees.id = orders.employee_id
WHERE orders.id IS NULL;
-- 21.2 แสดงลูกค้าที่ไม่มียอดซื้อ
SELECT customers.id AS customer_id, customers.name AS customer_name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.id IS NULL;

-- 22. 
-- จงหาชื่อลูกค้าและยอดซื้อโดยที่ยอดซื้อมีค่าสูงที่สุดในกลุ่มลูกค้าที่มียอดซื้อต่ำกว่าค่าเฉลี่ย
WITH CustomerTotalSales AS (
    SELECT customers.id AS customer_id,
           customers.name AS customer_name,
           SUM(order_items.amount * order_items.price * (1 - order_items.discount)) AS total_purchase
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
    JOIN order_items ON orders.id = order_items.order_id
    GROUP BY customers.id, customers.name
)

SELECT customer_id, customer_name, total_purchase
FROM CustomerTotalSales
WHERE total_purchase = (SELECT MAX(total_purchase) FROM CustomerTotalSales WHERE total_purchase < (SELECT AVG(total_purchase) FROM CustomerTotalSales));

-- 23. จงหาชื่อลูกค้าที่มีออเดอร์จากการซื้อแบบไม่ผ่านเซลล์มากสุด
SELECT customers.id AS customer_id,
       customers.name AS customer_name,
       COUNT(orders.id) AS total_orders
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.employee_id IS NULL
GROUP BY customers.id, customers.name
ORDER BY total_orders DESC
LIMIT 1;

-- 24. 
-- จงหาชื่อลูกค้าที่ไม่เคยซื้อสินค้าจากผู้ผลิตไท เอฟเวอเรสต์
SELECT customers.id AS customer_id,
       customers.name AS customer_name
FROM customers
WHERE customers.id NOT IN (
    SELECT DISTINCT customers.id
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
    JOIN order_items ON orders.id = order_items.order_id
    JOIN products ON order_items.product_id = products.id
    JOIN suppliers ON products.supplier_id = suppliers.id
    WHERE suppliers.name = 'ไท เอฟเวอเรสต์'
);

-- 25. 
-- จงหาชื่อลูกค้าที่เคยซื้อน้ำดื่มคชสารและน้ำดื่มแมมมอธ
SELECT DISTINCT customers.id AS customer_id,
                customers.name AS customer_name
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id
JOIN suppliers ON products.supplier_id = suppliers.id
WHERE products.name IN ('น้ำดื่มคชสาร', 'น้ำดื่มแมมมอธ');

-- 26. 
-- จงหาชื่อลูกค้าที่เคยซื้อไท อาเซียน แอตแลนติกแต่ไม่เคยซื้อไท เอฟเวอเรสต์
SELECT DISTINCT customers.id AS customer_id,
                customers.name AS customer_name
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id
JOIN suppliers ON products.supplier_id = suppliers.id
WHERE suppliers.name = 'ไท อาเซียน แอตแลนติก'
  AND customers.id NOT IN (
    SELECT customers.id
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
    JOIN order_items ON orders.id = order_items.order_id
    JOIN products ON order_items.product_id = products.id
    JOIN suppliers ON products.supplier_id = suppliers.id
    WHERE suppliers.name = 'ไท เอฟเวอเรสต์'
);