Common Table Expression (CTE) Patterns

-- Simple CTE for readability
WITH active_users AS (
  SELECT id, name, email, last_login
  FROM users
  WHERE status = 'active'
    AND last_login > CURRENT_DATE - INTERVAL '30 days'
),
user_orders AS (
  SELECT
    u.id AS user_id,
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
  FROM active_users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.created_at > CURRENT_DATE - INTERVAL '90 days'
  GROUP BY u.id, u.name
),
ranked AS (
  SELECT *,
    RANK() OVER (ORDER BY total_spent DESC) AS spend_rank
  FROM user_orders
)
SELECT name, order_count, total_spent, spend_rank
FROM ranked
WHERE spend_rank <= 10;