SQLRepGuidesPlaygroundLearning path

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.

try it

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.