Homework15+

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

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

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

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

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

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- 22. จงหาชื่อลูกค้าและยอดซื้อโดยที่ยอดซื้อมีค่าสูงที่สุดในกลุ่มลูกค้าที่มียอดซื้อต่ำกว่าค่าเฉลี่ย
WITH CustomerSales AS (
    SELECT c.id, c.name, SUM(oi.amount) AS total_amount
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    JOIN order_items oi ON oi.order_id = o.id
    GROUP BY c.id, c.name
),
AveragePurchase AS (
    SELECT AVG(total_amount) AS avg_purchase
    FROM CustomerSales
)
SELECT cs.id, cs.name, cs.total_amount
FROM CustomerSales cs
JOIN AveragePurchase ap ON cs.total_amount < ap.avg_purchase
ORDER BY cs.total_amount DESC
LIMIT 1;

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

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

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

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