CTEs and WITH RECURSIVE

Game: chapters VIII, IX and XII

Long queries are easier to write, read and check when they are built in named steps. A CTE is a named step.

WITH: naming a result

A common table expression, or CTE, is a query given a name at the top of a statement with WITH name AS (...). The rest of the statement can then use that name as if it were a table. Nothing is stored: the CTE exists only for the duration of the query. What it buys you is readability: instead of a subquery buried three levels deep, you get a list of steps, each with a name that says what it holds.

The examples use the bakery database from the bakery database page. Here the first step computes the value of each order line, its quantity times unit_price, the price paid on that line, and the main query adds those values up per order and keeps the orders of 20 or more.

WITH line AS (
  SELECT order_id, quantity * unit_price AS amount
  FROM order_item
)
SELECT order_id, ROUND(SUM(amount), 2) AS total
FROM line
GROUP BY order_id
HAVING SUM(amount) >= 20
ORDER BY total DESC, order_id;

The line's price, not today's. Three products cost more from Monday 16 March, and product.price holds only the current price, so a step that joined product and multiplied by p.price would value the earlier lines at a price nobody paid: order 10 would read 22.10 instead of 21.90, and order 12, which came to 19.60, would join the list at 20.00.

Several steps, and joining a CTE

Separate several CTEs with commas, under a single WITH. Each step can use the ones defined before it, and the final query can join them like ordinary tables. This is how a question with several parts becomes manageable: compute one fact per step, check each step on its own by selecting from it, then combine. What did each customer who had a refund pay over the four weeks, get back, and keep?

WITH paid AS (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS paid
  FROM orders AS o
  JOIN order_item AS oi ON oi.order_id = o.id
  GROUP BY o.customer_id
),
refunded AS (
  SELECT o.customer_id, SUM(r.amount) AS refunded
  FROM refund AS r
  JOIN orders AS o ON o.id = r.order_id
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(p.paid, 2) AS paid, ROUND(r.refunded, 2) AS refunded,
       ROUND(p.paid - r.refunded, 2) AS net
FROM refunded AS r
JOIN paid AS p ON p.customer_id = r.customer_id
JOIN customer AS c ON c.id = r.customer_id
ORDER BY c.id;

Each step adds up one table per customer: what they paid, from the order lines, and what they were given back, from refund. Joining the two only at the end is what keeps the figures right. Written as one query, with orders joined to both order_item and refund, every refund would repeat once per line of its order and every line once per refund of its order: Chloe Martin's refunds, 5.10, would add up to 10.20, and her payments to 129.60 instead of 120.60. With one row per customer in each step, the final join has nothing to multiply. It starts from refunded, so only the seven refunded customers appear; a LEFT JOIN from paid with COALESCE(r.refunded, 0) would list every customer who ordered.

To debug such a query, replace the final SELECT with SELECT * FROM paid and look at the intermediate result. Then do the same with the next step. A mistake usually shows up at the first step whose rows look wrong.

CTEs pair naturally with window functions: compute a rank or a previous value in a CTE, then filter on it outside, since a window function cannot appear in WHERE (see Order of execution).

WITH RECURSIVE: following a chain

Some questions have no fixed number of steps. "Who brought this customer to the shop, and who brought that one, and so on, all the way up?" might take one join or ten. A recursive CTE handles that. It has two parts joined by UNION ALL: an anchor, which produces the starting rows, and a recursive part, which refers to the CTE itself and produces the next rows from the previous ones. The database runs the anchor, then runs the recursive part again and again on the newest rows until it produces nothing.

In the bakery, customer.referred_by points to the customer who recommended the shop. Who brought Wen Petrov (id 12)? The anchor is Wen alone. Each round joins the customer whose id is the previous row's referred_by, one step further up, and the recursion stops at a customer who came on their own: a NULL referred_by matches no one, so the round adds no row.

WITH RECURSIVE chain(id, name, referred_by, steps) AS (
  SELECT id, name, referred_by, 0 FROM customer WHERE id = 12
  UNION ALL
  SELECT c.id, c.name, c.referred_by, ch.steps + 1
  FROM customer AS c
  JOIN chain AS ch ON c.id = ch.referred_by
)
SELECT name, steps FROM chain ORDER BY steps;

Four steps lead from Wen Petrov back to Alice Turner, the longest chain in the shop. To walk the other way, all the way down, turn the join round: c.referred_by = ch.id adds, each round, the customers the previous ones brought in. Started from Emma Novak, it finds Farid Haddad and Ugo Dubois, then Wen Petrov. The staff table holds the same kind of chain in staff.manager_id, three levels deep from the owner down to the counter.

Carrying extra columns through the recursion is a useful trick. A counter such as steps tells you how far each row is from the start, and a text column built up with || can record the whole path, which is often the answer you actually want.

WITH RECURSIVE: generating rows

A recursive CTE does not need a table at all. It can count, or produce every date of a period, which is handy for finding days that are missing from the data. The anchor gives the first day, the recursive part adds one to the day of the month and writes the date out as text (RIGHT('0' || n, 2) pads it to two digits), and a condition in its WHERE stops the recursion. Counting the day of the month only works within one month; real date arithmetic differs from one database to the next (PostgreSQL's CAST(day AS DATE) + 1 or generate_series, SQLite's date(day, '+1 day')), and the game's terminal refuses it (see Dates, substr and CAST).

WITH RECURSIVE calendar(n, day) AS (
  SELECT 2, '2026-03-02'
  UNION ALL
  SELECT n + 1, '2026-03-' || RIGHT('0' || (n + 1), 2) FROM calendar WHERE n < 8
)
SELECT cal.day, COUNT(o.id) AS orders
FROM calendar AS cal
LEFT JOIN orders AS o ON o.day = cal.day
GROUP BY cal.day
ORDER BY cal.day;

The left join keeps Friday 6 March, when the shop was closed, with a count of zero; grouping the orders table alone would simply have skipped it.

Common mistakes

Forgetting the stop condition, so that the recursion never ends: always make sure each round moves towards a limit, and while experimenting add a depth counter with a condition such as WHERE steps < 20. Following a chain that loops back on itself, which also never ends unless the path is tracked. Using UNION where UNION ALL is meant, or the reverse, without thinking about duplicates (see UNION, INTERSECT and EXCEPT). Putting the recursive reference in a subquery or an aggregate, which SQLite does not allow. Writing the anchor and the recursive part with a different number of columns.

Try it: Above their category, Word of mouth, Money back and Shutters down, on the exercises page.

In the game: Chapter VIII, The First Order Before Dawn, and Chapter IX, The Trunk, use a CTE; Chapter XII, Follow the Money, practises WITH RECURSIVE.