The WITH clause names a query so you can build on it. Watch a nested subquery unfold into a readable pipeline of steps — then run a recursive CTE one iteration at a time.
A common table expression (CTE) is introduced with WITH. It can make a multi-step query easier to read because each step gets a name and can refer to earlier steps. A CTE exists for that statement; it does not create a permanent table.
Here the first query groups orders, and the outer query filters those grouped results. The same logic could be written as a subquery; choose the form that makes the data flow easiest to follow. See subqueries.
A CTE is a way to structure SQL, not automatically a temporary table or an optimization fence. Engines may inline, materialize, or otherwise transform it. PostgreSQL's behavior depends on features such as whether the CTE is referenced more than once and whether it is marked MATERIALIZED; SQLite also has materialization hints. Check the plan for the database and version you deploy.
Avoid splitting a short query into many one-use CTEs if the names obscure rather than clarify the operation. A CTE can also be referenced multiple times, but repeated work and materialization choices should be measured.
A recursive CTE repeatedly applies a query to rows produced by an earlier iteration. It has an anchor query for the starting rows and a recursive term for the next rows. This is useful for hierarchies, graph traversal, and generated sequences. It must have a termination condition or a guard such as a depth limit; cycles can otherwise keep producing rows. For a full walk-through, see recursive CTEs.
A chain of CTEs is useful when each step has a name that describes a transformation: filtered rows, grouped totals, then a final selection. Keep the sequence short enough that a reader can follow the data from the first CTE to the last. If a CTE is referenced several times, verify whether the engine recomputes it or materializes it. A CTE is also scoped to one statement; if the result must persist or be shared across statements, create a temporary or permanent table instead.
About to run: press Step. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “A query with named steps”?
What does this module say about “CTEs do not guarantee faster execution”?
What does this module say about “Recursive CTEs”?
Name a subquery with WITH so the logic reads top to bottom, and see how a recursive CTE walks a tree row by row.