What are CTEs (Common Table Expressions)?

WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE status = 'active'
)
SELECT * FROM active_users WHERE name LIKE 'J%';

WITH
  active_users AS (
    SELECT * FROM users WHERE status = 'active'
  ),
  user_orders AS (
    SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent
    FROM orders
    GROUP BY user_id
  )
SELECT
  u.name,
  uo.order_count,
  uo.total_spent
FROM active_users u
JOIN user_orders uo ON u.id = uo.user_id
ORDER BY uo.total_spent DESC;

-- Employee hierarchy
WITH RECURSIVE emp_hierarchy AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees with managers
  SELECT e.id, e.name, e.manager_id, h.level + 1
  FROM employees e
  JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM emp_hierarchy ORDER BY level, name;

-- Generate number series
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- CTE (more readable)
WITH order_totals AS (
  SELECT user_id, SUM(total) as total_spent
  FROM orders GROUP BY user_id
)
SELECT * FROM users u
JOIN order_totals ot ON u.id = ot.user_id;

-- Subquery (harder to read)
SELECT * FROM users u
JOIN (
  SELECT user_id, SUM(total) as total_spent
  FROM orders GROUP BY user_id
) ot ON u.id = ot.user_id;