Common Table Expressions (CTEs) and Recursion

A common table expression, or CTE, lets you give a name to an intermediate query result and then use that name in a later query. Mechanically it is a subquery that you can read top to bottom. Practically it is one of the biggest jumps in SQL readability you can make.

Basic shape

WITH recent_orders AS (
  SELECT user_id, SUM(total) AS spend_30d
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, r.spend_30d
FROM users u
JOIN recent_orders r ON r.user_id = u.id
ORDER BY r.spend_30d DESC
LIMIT 20;

The part inside WITH ... AS ( ... ) is just a named subquery. The main query below is where you consume it.

CTE vs subquery

Every CTE can be rewritten as an inline subquery. What CTEs buy you is:

Linear reading order. You read the steps top to bottom, not inside-out.
Names for intermediates. Your future self understands the query faster.
Multiple references. You can use the same CTE twice in the main query without repeating the expression.

Query planners used to materialize CTEs eagerly, which made them slower than subqueries. Modern PostgreSQL, SQL Server, and Snowflake generally inline CTEs unless you force materialization. Treat that as an implementation detail; write the clearest query first, profile if it matters.

Multiple CTEs

WITH
  active_users AS (
    SELECT id FROM users WHERE last_login > NOW() - INTERVAL '7 days'
  ),
  recent_orders AS (
    SELECT user_id, COUNT(*) AS n
    FROM orders
    WHERE created_at > NOW() - INTERVAL '7 days'
    GROUP BY user_id
  )
SELECT u.id, COALESCE(r.n, 0) AS orders_7d
FROM active_users u
LEFT JOIN recent_orders r ON r.user_id = u.id;

You can define any number of CTEs and each one may reference the ones before it.

Recursive CTEs

Recursive CTEs traverse hierarchical data: org charts, category trees, threaded comments, bill of materials. The shape is always the same: an anchor query plus a recursive query unioned together.

-- Every manager above a given employee
WITH RECURSIVE chain AS (
  SELECT id, manager_id, name, 0 AS depth
  FROM employees
  WHERE id = 42                       -- anchor: the employee we start from

  UNION ALL

  SELECT e.id, e.manager_id, e.name, c.depth + 1
  FROM employees e
  JOIN chain c ON c.manager_id = e.id -- recursive step
)
SELECT * FROM chain ORDER BY depth;

Rules of thumb for recursive CTEs:

Always guard with a depth cutoff or cycle check on data you do not fully trust. Cycles will loop forever.
Use UNION ALL, not UNION. UNION deduplicates on every iteration and is dramatically slower.
The recursive part must reference the CTE itself; the anchor part must not.

Common mistakes

Forgetting a , between multiple CTEs.
Putting a semicolon before the main query. The CTE and the query that consumes it are one statement.
Writing a recursive CTE without a base case. Your query runs forever or hits the recursion limit.

Practice this live

The Tower chapter in SQL Protocol introduces both views and CTEs. Interview Mode Floor 3 almost always contains a top-N-per-group question that is trivial with a window function and ugly without one; CTEs keep those queries readable.

CTEs: Readable SQL at Scale