การบ้าน15

-- 17. แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด  
SELECT c.name AS customer_name, p.desc AS product_name, SUM(oi.amount * (oi.price - oi.discount)) AS total_purchase
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
GROUP BY c.name, p.desc;


-- 18. แสดงชื่อลูกค้าที่มียอดสั่งไฮยีน่ามากสุด 5 อันดับแรก
SELECT c.name AS customer_name, SUM(oi.amount * (oi.price - oi.discount)) AS total_purchase
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.name
ORDER BY total_purchase DESC
LIMIT 5;


-- 19. จงหาชื่อ customer ที่มียอดสั่งซื้อมากกว่า 10000
SELECT c.name AS customer_name, SUM(oi.amount * (oi.price - oi.discount)) AS total_purchase
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.name
HAVING total_purchase > 10000;


-- 20. แสดงชื่อเซลล์และยอดขาย เฉพาะคนที่ยอดขายมากกว่าค่าเฉลี่ย (ยอดรวม / ตามจำนวน sales (ฝ่ายขาย))
SELECT e.name AS salesperson_name, SUM(oi.amount * (oi.price - oi.discount)) AS total_sales
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.name
HAVING total_sales > (SELECT AVG(total_sales) FROM (SELECT e.id, SUM(oi.amount * (oi.price - oi.discount)) AS total_sales
                                                     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 avg_sales);


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


-- แสดงลูกค้าที่ไม่มียอดซื้อ
SELECT c.name AS customer_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
WHERE oi.id IS NULL;


-- 22. จงหาชื่อลูกค้าและยอดซื้อโดยที่ยอดซื้อมีค่าสูงที่สุดในกลุ่มลูกค้าที่มียอดซื้อต่ำกว่าค่าเฉลี่ย
SELECT customer_name, MAX(total_purchase) AS max_purchase
FROM (
    SELECT c.id, c.name AS customer_name, SUM(oi.amount * (oi.price - oi.discount)) AS total_purchase
    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_purchase < (
        SELECT AVG(total_purchase)
        FROM (
            SELECT c.id, SUM(oi.amount * (oi.price - oi.discount)) AS total_purchase
            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
        ) AS avg_purchase
    )
) AS customer_purchases
GROUP BY customer_name
LIMIT 0, 1000;


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


-- 24. จงหาชื่อลูกค้าที่ไม่เคยซื้อสินค้าจากผู้ผลิตไท เอฟเวอเรสต์
SELECT DISTINCT c.name AS customer_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 <> 'ไท เอฟเวอเรสต์';


-- 25. จงหาชื่อลูกค้าที่เคยซื้อน้ำดื่มคชสารและน้ำดื่มแมมมอธ
SELECT c.name AS customer_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
WHERE p.desc IN ('น้ำดื่มคชสาร', 'น้ำดื่มแมมมอ
ธ');

-- 26. จงหาชื่อลูกค้าที่เคยซื้อไท อาเซียน แอตแลนติกแต่ไม่เคยซื้อไท เอฟเวอเรสต์
SELECT c.name AS customer_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 = 'ไท เอฟเวอเรสต์'
);