Common Table Expression (CTE): SQL WITH Clause Explained

-- Basic common table expression
WITH active_users AS (
  SELECT user_id, name, email
  FROM users
  WHERE status = 'active'
    AND last_login >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  au.name,
  COUNT(o.order_id) AS recent_orders
FROM active_users au
LEFT JOIN orders o ON au.user_id = o.user_id
GROUP BY au.name
ORDER BY recent_orders DESC;

-- Syntax template
WITH cte_name [(column1, column2, ...)] AS (
  SELECT ...
)
SELECT ... FROM cte_name;

-- Multiple CTEs (comma-separated, one WITH)
WITH
  step1 AS (
    SELECT ...
  ),
  step2 AS (
    SELECT ... FROM step1
  ),
  step3 AS (
    SELECT ... FROM step2
  )
SELECT * FROM step3;

WITH recent_orders AS (
  SELECT user_id, amount
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM recent_orders
GROUP BY user_id
ORDER BY total_spent DESC;

The first CTE filters to the relevant rows. The main query aggregates. This separates the "what data" question from the "what metric" question.

WITH dept_salaries AS (
  SELECT
    department,
    name,
    salary
  FROM employees
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY department ORDER BY salary DESC
    ) AS rn
  FROM dept_salaries
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 3;

The first CTE computes per-group aggregates. The second CTE (or the main query) ranks groups. This is the standard pattern for top-N per group problems.

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  curr.month,
  curr.revenue AS current_revenue,
  prev.revenue AS previous_revenue,
  curr.revenue - prev.revenue AS mom_change
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
  ON curr.month = prev.month + INTERVAL '1 month'
ORDER BY curr.month;

Define the CTE once and reference it twice in the main query. This avoids duplicating a complex subquery for a self-join.

WITH RECURSIVE org_tree AS (
  -- Anchor: CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 20
)
SELECT * FROM org_tree ORDER BY depth, name;

A recursive CTE references itself. It has an anchor member (base case) and a recursive member, connected by UNION ALL. The engine iterates until the recursive member returns zero rows.

-- PostgreSQL 12+: force materialization
WITH expensive_calc AS MATERIALIZED (
  SELECT user_id, heavy_computation(data) AS result
  FROM big_table
)
SELECT * FROM expensive_calc WHERE result > 100;

-- PostgreSQL 12+: force inlining
WITH filtered AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM filtered WHERE amount > 1000;