Untitled

-- 17. แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด  
SELECT c.id,c.name,p.name,oi.amount,oi.price
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 18. แสดงชื่อลูกค้าที่มียอดสั่งไฮยีน่ามากสุด 5 อันดับแรก
SELECT c.id,c.name,SUM(oi.amount * oi.price) as `total`
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name
ORDER BY `total` DESC;

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

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

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

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

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

-- 24
SELECT DISTINCT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
LEFT JOIN suppliers s ON p.supplier_id = s.id
WHERE s.name <> 'ไท เอฟเวอเรสต์' OR s.id IS NULL;

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

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