MySQL Subqueries

-- Scalar subquery: returns a single value
-- Find products priced above the average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

-- IN with subquery: find customers who have placed at least one order
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id FROM orders
);

-- NOT IN with subquery: find customers who have NEVER placed an order
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- Subquery with comparison: find the most expensive product
SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

-- Derived table: customers with their order count and total spent
SELECT
    d.customer_id,
    d.order_count,
    d.total_spent,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name
FROM (
    SELECT
        customer_id,
        COUNT(*)   AS order_count,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS d
INNER JOIN customers c ON d.customer_id = c.customer_id
WHERE d.order_count >= 2
ORDER BY d.total_spent DESC;

-- Subquery in SELECT clause (scalar subquery per row)
-- Show each product with the count of times it has been ordered
SELECT
    p.product_id,
    p.name,
    p.price,
    (
        SELECT SUM(oi.quantity)
        FROM order_items oi
        WHERE oi.product_id = p.product_id
    ) AS total_ordered
FROM products p
ORDER BY total_ordered DESC;

-- EXISTS: find customers who have at least one delivered order
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id   -- correlated: references outer query
      AND o.status = 'delivered'
);

-- NOT EXISTS: find customers with no orders at all
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Correlated subquery: find each customer's most recent order
SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    o.total,
    o.ordered_at
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.ordered_at = (
    SELECT MAX(o2.ordered_at)
    FROM orders o2
    WHERE o2.customer_id = c.customer_id  -- correlated
);

-- Simple CTE: customer order summary
WITH customer_summary AS (
    SELECT
        customer_id,
        COUNT(*)   AS order_count,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.first_name,
    c.last_name,
    cs.order_count,
    cs.total_spent
FROM customers c
INNER JOIN customer_summary cs ON c.customer_id = cs.customer_id
WHERE cs.total_spent > 100
ORDER BY cs.total_spent DESC;

-- Multiple CTEs in one query
WITH
    top_products AS (
        SELECT product_id, SUM(quantity) AS total_sold
        FROM order_items
        GROUP BY product_id
        ORDER BY total_sold DESC
        LIMIT 3
    ),
    product_revenue AS (
        SELECT product_id, SUM(quantity * unit_price) AS revenue
        FROM order_items
        GROUP BY product_id
    )
SELECT
    p.name,
    tp.total_sold,
    pr.revenue
FROM products p
INNER JOIN top_products    tp ON p.product_id = tp.product_id
INNER JOIN product_revenue pr ON p.product_id = pr.product_id
ORDER BY tp.total_sold DESC;