SQL CTEs (WITH clause) and recursive CTEs
A common table expression (CTE) names a subquery with WITH so the main query can use it like a table. It's the cleanest way to break a hard question into steps.
Basic CTE
Write each step as a named CTE, then query them. You can chain several, separated by commas, and later ones can use earlier ones.
WITH firsts AS ( SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id ) SELECT c.name, f.first_order FROM customers c JOIN firsts f ON f.customer_id = c.id;
Recursive CTE
WITH RECURSIVE has a starting query, UNION ALL, and a step that joins back to the CTE itself. Postgres repeats the step until it returns no rows, which is how you walk a manager chain of any depth.
WITH RECURSIVE chain AS ( SELECT id, name, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, c.level + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT * FROM chain;
Try it
This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Common mistakes
- Forgetting RECURSIVE on a self-referencing CTE.
- A recursive step with no stopping point, which loops until the query times out.
- Repeating the same subquery three times instead of naming it once.
Practice drills
Reading it isn't enough. Solve these and it'll still be there in an interview.