SQL common table expressions (CTEs) -- readable complex queries

CTEs for readable complex queries

-- Instead of nested subqueries:
WITH
  active_users AS (
    SELECT id, name, email
    FROM users
    WHERE last_login > NOW() - interval '30 days'
  ),
  user_orders AS (
    SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent
    FROM orders
    WHERE created_at > NOW() - interval '90 days'
    GROUP BY user_id
  ),
  high_value AS (
    SELECT u.*, uo.order_count, uo.total_spent
    FROM active_users u
    JOIN user_orders uo ON uo.user_id = u.id
    WHERE uo.total_spent > 1000
  )
SELECT name, email, order_count, total_spent
FROM high_value
ORDER BY total_spent DESC;

-- CTE used multiple times (DRY)
WITH monthly AS (
  SELECT date_trunc('month', created_at) as month, SUM(total) as revenue
  FROM orders GROUP BY 1
)
SELECT
  m.month, m.revenue,
  m.revenue - LAG(m.revenue) OVER (ORDER BY m.month) as growth
FROM monthly m;