Subquery Patterns — Scalar, Correlated, Derived

-- Scalar subquery in SELECT
SELECT p.name, p.price,
  (SELECT AVG(price) FROM products
   WHERE category_id = p.category_id) AS category_avg
FROM products p;

-- Correlated subquery
SELECT c.name, c.email, o.total, o.created_at
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at = (
  SELECT MAX(o2.created_at)
  FROM orders o2
  WHERE o2.customer_id = c.id
);

-- Derived table
SELECT category, avg_price, product_count
FROM (
  SELECT category, AVG(price) AS avg_price,
    COUNT(*) AS product_count
  FROM products
  GROUP BY category
) AS summary
WHERE product_count > 5
ORDER BY avg_price DESC;

-- EXISTS semi-join
SELECT c.name, c.email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 100
    AND o.created_at > '2024-01-01'
);