Untitled

-- 17. แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด  
SELECT
    customers.name AS CustomerName,
    products.name AS ProductName,
    format(SUM(order_items.price),2) AS TotalQuantity
FROM
    customers
INNER JOIN
    orders ON customers.id = orders.customer_id
INNER JOIN
    order_items ON orders.id = order_items.order_id
INNER JOIN
    products ON order_items.product_id = products.id
GROUP BY
    customers.name, products.name;

-- 18. แสดงชื่อลูกค้าที่มียอดสั่งไฮยีน่ามากสุด 5 อันดับแรก
SELECT
    customers.name AS CustomerName,
    format(sum(order_items.price),2) AS TotalQuantity
FROM
    customers
INNER JOIN
    orders ON customers.id = orders.customer_id
INNER JOIN
    order_items ON orders.id = order_items.order_id
GROUP BY
    customers.name
ORDER BY
    TotalQuantity DESC
LIMIT 5;

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

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

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

-- 22. จงหาชื่อลูกค้าและยอดซื้อโดยที่ยอดซื้อมีค่าสูงที่สุดในกลุ่มลูกค้าที่มียอดซื้อต่ำกว่าค่าเฉลี่ย
SELECT 
    customer_totals.CustomerName,
    MAX(customer_totals.total_purchase) AS MaxPurchase
FROM
    (SELECT 
        customers.id,
            customers.name AS CustomerName,
            SUM(order_items.price) AS total_purchase
    FROM
        customers
    LEFT JOIN orders ON customers.id = orders.customer_id
    LEFT JOIN order_items ON orders.id = order_items.order_id
    GROUP BY customers.id , customers.name
    HAVING total_purchase < (SELECT 
            AVG(sub_total)
        FROM
            (SELECT 
            SUM(order_items.price) AS sub_total
        FROM
            orders
        INNER JOIN order_items ON orders.id = order_items.order_id
        GROUP BY orders.customer_id) AS avg_purchase)) AS customer_totals
GROUP BY customer_totals.id , customer_totals.CustomerName;

-- 23. จงหาชื่อลูกค้าที่มีออเดอร์จากการซื้อแบบไม่ผ่านเซลล์มากสุด
SELECT
    customers.name AS CustomerName,
    COUNT(orders.id) AS OrderCount
FROM
    customers
LEFT JOIN
    orders ON customers.id = orders.customer_id
WHERE
    NOT EXISTS (
        SELECT 1
        FROM order_items
        WHERE order_items.order_id = orders.id
    )
GROUP BY
    customers.id, customers.name
ORDER BY
    OrderCount DESC;

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

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

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