Untitled

-- 17. แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด
SELECT
    c.name AS customer_name,
    p.name AS product_name,
    oi.amount * oi.price AS total_order_amount
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.name AS customer_name,
    SUM(oi.amount * oi.price) AS total_order_amount
FROM
    customers c
JOIN
    orders o ON c.id = o.customer_id
JOIN
    order_items oi ON o.id = oi.order_id
WHERE
    oi.product_id = 10 -- ID ของสินค้าไฮยีน่า
GROUP BY
    c.name
ORDER BY
    total_order_amount DESC
LIMIT 5;


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


-- 20. แสดงชื่อเซลล์และยอดขาย เฉพาะคนที่ยอดขายมากกว่าค่าเฉลี่ย (ยอดรวม / จำนวนขาย)
SELECT
    e.name AS employee_name,
    SUM(oi.amount * oi.price) AS total_sales
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
    o.date IS NOT NULL
GROUP BY
    e.name
HAVING
    total_sales > (SELECT AVG(total_sales) FROM (SELECT SUM(oi.amount * oi.price) AS total_sales FROM order_items oi GROUP BY oi.order_id) AS avg_sales);


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

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


-- 22. จงหาชื่อลูกค้าและยอดซื้อโดยที่ยอดซื้อมีค่าสูงที่สุดในกลุ่มลูกค้าที่มียอดซื้อต่ำกว่าค่าเฉลี่ย
WITH CustomerTotalOrder AS (
    SELECT
        c.name AS customer_name,
        SUM(oi.amount * oi.price) AS total_order_amount
    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
)
SELECT
    customer_name,
    total_order_amount
FROM
    CustomerTotalOrder
WHERE
    total_order_amount = (SELECT MAX(total_order_amount) FROM CustomerTotalOrder WHERE total_order_amount < (SELECT AVG(total_order_amount) FROM CustomerTotalOrder));


-- 23. จงหาชื่อลูกค้าที่มีออเดอร์จากการซื้อแบบไม่ผ่านเซลล์มากสุด
SELECT
    c.name AS customer_name,
    COUNT(o.employee_id) AS total_orders
FROM
    customers c
JOIN
    orders o ON c.id = o.customer_id
WHERE
    o.employee_id IS NULL
GROUP BY
    c.name
ORDER BY
    total_orders 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.name 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.name NOT IN (
        SELECT DISTINCT
            c2.name
        FROM
            customers c2
        JOIN
            orders o2 ON c2.id = o2.customer_id
        JOIN
            order_items oi2 ON o2.id = oi2.order_id
        JOIN
            products p2 ON oi2.product_id = p2.id
        JOIN
            suppliers s2 ON p2.supplier_id = s2.id
        WHERE
            s2.name = 'ไท เอฟเวอเรสต์'
    );