Untitled

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

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

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


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

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

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