Subqueries & CTEs

-- Scalar subquery (returns one value)
SELECT name, price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

-- Subquery in WHERE (multi-row)
SELECT * FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id FROM orders
    WHERE total > 100
);

-- Correlated subquery (references outer query)
SELECT c.name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- EXISTS (more efficient than IN for large datasets)
SELECT c.name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.total > 500
);

-- CTE: Common Table Expression (cleaner than subqueries)
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS month,
        SUM(total) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
)
SELECT month, revenue, order_count,
    revenue / order_count AS avg_order_value
FROM monthly_sales
WHERE revenue > 10000
ORDER BY month DESC;

-- Multiple CTEs
WITH
  active_customers AS (
    SELECT DISTINCT customer_id FROM orders
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  ),
  customer_totals AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
  )
SELECT c.name, ct.total_spent
FROM customers c
JOIN active_customers ac ON c.id = ac.customer_id
JOIN customer_totals ct ON c.id = ct.customer_id
ORDER BY ct.total_spent DESC;

-- Recursive CTE (hierarchical data)
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;