Can a CTE in a WITH clause reference another CTE, and does definition order matter?
answer
- the list is read left to right
- a later step may use an earlier one
- try defining them in the wrong order
- forward reference means unknown table
- only recursion may look backwards
basics
~20 sYes. A CTE may reference any CTE defined earlier in the same WITH list, which is how multi-step queries are stacked. Order matters: referring to a name defined later fails unless the CTE is recursive.
solid answer
~40 sWithin one `WITH` clause the definitions are visible left to right: the second CTE can select from the first, the third from either, and the main statement from all of them. That is exactly how you stack a pipeline — filter, then aggregate, then rank — with each step named. The scoping is ordered, so a **forward reference** fails: `WITH b AS (SELECT * FROM a), a AS (...)` raises an unknown-table error because `a` is not yet in scope when `b` is compiled. The one exception is a self-reference in a recursive CTE, which needs the `RECURSIVE` keyword. Chaining also means an earlier CTE can be referenced by several later steps, which is a large part of why naming a repeated subquery reads better than pasting it twice.
code
sql · 13 linesWITH paid AS (
SELECT customer_id, region, total
FROM orders
WHERE status = 'PAID'
),
per_customer AS (
SELECT customer_id, region, SUM(total) AS spend
FROM paid
GROUP BY customer_id, region
)
SELECT region, AVG(spend) AS avg_spend
FROM per_customer
GROUP BY region;go deeper
Know that you can write several CTEs separated by commas and that a later one may select from an earlier one; try writing a two-step chain by hand.
Explain the left-to-right scoping rule and what happens on a forward reference, including the dangerous case where a real table of that name silently takes over.
Show how you use a chain for diagnosis — pointing the final statement at an intermediate step — and where a long chain has stopped aiding readability.
Discuss how far a chain should grow before a step deserves to become a shared, tested artefact rather than being re-derived inside every analytics query.
## Ordered visibility inside one WITH list A `WITH` clause holds a comma-separated list of definitions, and the scoping rule is simple: **each definition can see the ones written before it**, and the statement at the end can see all of them. ```sql WITH paid AS ( SELECT customer_id, region, total FROM orders WHERE status = 'PAID' ), per_customer AS ( SELECT customer_id, region, SUM(total) AS spend FROM paid GROUP BY customer_id, region ), region_avg AS ( SELECT region, AVG(spend) AS avg_spend FROM per_customer GROUP BY region ) SELECT c.customer_id, c.spend, r.avg_spend FROM per_customer c JOIN region_avg r ON r.region = c.region WHERE c.spend > r.avg_spend; ``` Three named steps, each built from the previous one, and the final statement joins two of them. `per_customer` is referenced twice — once by `region_avg` and once by the main query — and it is written once. ## Forward references fail Order is not cosmetic. If you write ```sql WITH b AS (SELECT * FROM a), a AS (SELECT 1 AS x) SELECT * FROM b; ``` the engine compiles `b` first, finds no table or CTE called `a` in scope, and raises an error — or worse, silently resolves `a` to a *real* table of that name if one exists, which produces a wrong answer instead of an error. Define in dependency order, top to bottom, which is also the order a reader wants to read. ## The recursive exception The only way a CTE may refer to a name not yet fully defined is a recursive CTE referring to *itself*, introduced with `WITH RECURSIVE`. That is a separate mechanism with its own anchor and recursive members; the plain ordered-visibility rule above governs everything non-recursive. ## What chaining buys you Stacking is the practical reason `WITH` exists. Compare it with derived tables, where reusing a step means writing the subquery twice, once in each place — and then keeping the two copies in sync forever. With CTEs the step has one definition and one name. When a reviewer asks "what counts as an active customer here?" the answer is a single named block near the top of the query. A second benefit is diagnosis. To debug a stacked query you temporarily replace the final statement with `SELECT * FROM per_customer` and look at that intermediate step; nothing else about the query has to change. Debugging a four-level nested subquery means carefully extracting the inner parentheses by hand. ## Where chaining stops helping A long chain is still one statement. Ten stacked CTEs where each is used once, and where the whole thing could be three, is not more readable than the shorter version — it is a pipeline written at the wrong granularity. Name the steps a domain expert would name, not every syntactic stage. Also remember the whole `WITH` list shares one namespace: two definitions cannot carry the same name, and a definition that nothing references is dead code a reviewer will correctly ask you to delete.
- Can the main statement reference a CTE that no other CTE uses?Yes — every name in the WITH list is in scope for the final statement, regardless of whether other CTEs use it. A CTE referenced by nothing at all is simply unused code and should be deleted rather than left in the query.
- How do you find which stacked CTE introduced a wrong row count?Keep the WITH list intact and replace the final statement with SELECT * FROM that step, or a COUNT over it. Because each step is named and self-contained, you can walk down the chain one step at a time without editing the definitions themselves.
saying these in an interview costs you the question
- Says the order of CTE definitions never matters
- Thinks each CTE can only be used by the final query
- Claims a later CTE cannot be joined to an earlier one
- Believes forward references work without RECURSIVE
- Writes the same subquery twice instead of naming it once