Common table expressions

WITH, chained CTEs, and recursion.

What you'll be able to do

Refactor nesting into named steps
Chain several CTEs
Write a recursive CTE

The WITH clause

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS mth,
         SUM(total) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT * FROM monthly WHERE revenue > 100000;

A CTE is a named result set defined before the query that uses it, so the logic reads top to bottom instead of inside out.

Chaining

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS mth, SUM(total) AS revenue
  FROM orders GROUP BY 1
),
with_growth AS (
  SELECT mth, revenue,
         revenue - LAG(revenue) OVER (ORDER BY mth) AS mom_change
  FROM monthly
)
SELECT * FROM with_growth WHERE mom_change < 0;

Each CTE can reference the ones before it. This is the single biggest readability win available in SQL, and it is what interviewers hope to see on a multi-step problem.

Recursive CTEs

WITH RECURSIVE chain AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY depth;

An anchor member, UNION ALL, then a recursive member referencing the CTE. Used for org charts, category trees and generating date series.

Make sure it terminates. On data you do not fully trust, add a depth guard, or a cycle will run until the engine stops it.

Performance note

A CTE is not automatically faster. Older PostgreSQL always materialised them, acting as an optimisation fence; modern versions inline unless you write MATERIALIZED. Treat CTEs as a readability tool, and check the plan if performance matters.

Key points

CTEs turn inside-out nesting into readable named steps
A recursive CTE needs an anchor, UNION ALL, and a terminating condition
CTEs are for readability — they are not inherently faster

Check yourself