SQL / SUBQUERIES AND CTES
Chaining CTEs to stage a complex query
Build a query as a series of named stages in one WITH list, each reading the one before, so you can filter, re-aggregate and join results step by step.
What you will learn
- Write several stages in one WITH list, comma separated, and reuse earlier names
- Order stages by dependency: a CTE can only read the CTEs written above it
- Aggregate an aggregate by grouping in one stage and grouping its output in the next
- Debug a chain by pointing the final SELECT at an intermediate stage
Understanding Chaining CTEs to stage a complex query
A chain is one WITH keyword followed by several named stages separated by commas, each stage a complete SELECT in parentheses. What makes it a chain rather than a list is that a stage may select from any stage written above it by name, so per_customer can read paid and overall can read per_customer. The result has the same shape as stacking derived tables inside one another, but it is written flat and top to bottom, with a name on every intermediate result instead of a wall of closing parentheses.
Each stage is its own query level, and that is the mechanical payoff. Once SUM(amount) has been computed and named total in one stage, the next stage sees total as an ordinary column: it can test it in WHERE, feed it to AVG, or join it back to detail rows. That is why a chain expresses things a single SELECT cannot, such as averaging per-customer sums, since an aggregate cannot take another aggregate as its argument at the same level.
Names come into scope in the order they are written, so the list is ordered by dependency and a stage that refers to a name defined further down will not resolve. Those names disappear when the statement ends. Because a stage is only a name, the final SELECT can read several stages at once, and can read the same stage more than once, which the example below does when it joins per_customer to the single-row overall. Treat the chain as logical structure rather than an execution plan: the optimizer is free to fold stages together, so chaining buys clarity and expressive power more than speed.
placeholder
CREATE TABLE orders (
order_id INTEGER,
customer TEXT,
status TEXT,
amount INTEGER
);
INSERT INTO orders VALUES
(1, 'ana', 'paid', 120),
(2, 'ana', 'paid', 80),
(3, 'ana', 'refunded', 200),
(4, 'ben', 'paid', 300),
(5, 'ben', 'paid', 50),
(6, 'cleo', 'paid', 40),
(7, 'cleo', 'paid', 60),
(8, 'dan', 'refunded', 500);
WITH paid AS (
SELECT customer, amount
FROM orders
WHERE status = 'paid'
),
per_customer AS (
SELECT customer, SUM(amount) AS total
FROM paid
GROUP BY customer
),
overall AS (
SELECT AVG(total) AS avg_total
FROM per_customer
)
SELECT p.customer,
p.total,
ROUND(o.avg_total, 2) AS avg_total,
CASE WHEN p.total > o.avg_total THEN 'above' ELSE 'below' END AS vs_avg
FROM per_customer p
CROSS JOIN overall o
ORDER BY p.total DESC;A chained WITH list is a pipeline of named query levels, where every stage sees the finished output of the stages above it as plain columns.
Worked examples
Reusing one stage twice
A single aggregation stage is joined to itself so two months land in the same row.
CREATE TABLE readings (
sensor TEXT,
month TEXT,
value INTEGER
);
INSERT INTO readings VALUES
('a', '2024-01', 10),
('a', '2024-01', 14),
('a', '2024-02', 30),
('b', '2024-01', 8),
('b', '2024-02', 4),
('b', '2024-02', 4);
WITH monthly AS (
SELECT sensor, month, SUM(value) AS total
FROM readings
GROUP BY sensor, month
),
paired AS (
SELECT j.sensor,
j.total AS jan_total,
f.total AS feb_total
FROM monthly j
JOIN monthly f
ON f.sensor = j.sensor
AND j.month = '2024-01'
AND f.month = '2024-02'
)
SELECT sensor, jan_total, feb_total, feb_total - jan_total AS change
FROM paired
ORDER BY sensor;Example explained
Line 1monthly is written once and read twice, as j and f, which is only possible because it has a name.
Line 2The month tests sit in the ON clause, pinning one side of the join to January and the other to February instead of filtering both away.
Line 3paired has one row per sensor, so feb_total - jan_total in the final SELECT is arithmetic on columns, not a nested aggregate.
Line 4Sensor b has two February rows, already summed to 8 by monthly, so the later stages cannot double count them.
Aggregating an aggregate
One stage sums per order, the next stage summarises those sums, which a single SELECT cannot do.
CREATE TABLE line_items (
order_id INTEGER,
sku TEXT,
qty INTEGER
);
INSERT INTO line_items VALUES
(1, 'pen', 3),
(1, 'pad', 1),
(2, 'pen', 10),
(3, 'pad', 2),
(3, 'ink', 2),
(3, 'pen', 1),
(4, 'ink', 5);
WITH order_qty AS (
SELECT order_id, SUM(qty) AS items
FROM line_items
GROUP BY order_id
),
sizes AS (
SELECT COUNT(*) AS orders,
MIN(items) AS smallest,
MAX(items) AS largest,
AVG(items) AS mean_items
FROM order_qty
)
SELECT orders, smallest, largest, ROUND(mean_items, 2) AS mean_items
FROM sizes;Example explained
Line 1order_qty changes the grain from one row per line item to one row per order; that change is the reason the stage exists.
Line 2sizes aggregates order_qty, so AVG receives the plain column items rather than a SUM expression.
Line 3Writing AVG(SUM(qty)) in one SELECT is rejected, because an aggregate cannot take another aggregate as its argument at the same query level.
Line 4COUNT(*) returns 4 orders, not 7 line items, since it counts rows of the previous stage.
Important notes
CTE names live only for the statement that defines them, so a following statement that selects from paid fails with an unknown table; they are not temporary tables.
Referencing a stage twice does not guarantee it is computed once; Postgres inlines single-use non-recursive CTEs and accepts AS MATERIALIZED when you need one evaluation.
Common mistakes
Repeating the keyword, as in WITH a AS (...), WITH b AS (...); the parser fails at the second WITH, because one WITH introduces the whole comma-separated list.
Selecting from a stage that is defined further down the list, which fails with an unknown-table error since a non-recursive WITH exposes names only downward.
Leaving a comma after the last stage's closing parenthesis, so the engine expects another CTE name and reports a syntax error at the final SELECT.
Try it yourself
Change, predict, then run
Using the orders table from the main example, add a fourth stage named top_customer that keeps only the per_customer row with the highest total. Have the final SELECT report that customer, their total, and their share of all paid revenue as a percentage rounded to one decimal place.
Open the SQL workspaceCheck your understanding
A statement reads WITH a AS (...), b AS (SELECT ... FROM a), c AS (SELECT ... FROM b) followed by a final SELECT. Which statement about what each part can reference is true?
- Every CTE can select from every other one, because they all belong to the same WITH clause.
- Only the final SELECT may read the CTEs; b cannot read a.
- c can read both b and a, but a cannot read b or c.
- Each CTE may be read only once, so if b reads a then the final SELECT cannot read a.
Show answer
Names in a non-recursive WITH become visible in the order they are written, so each stage sees only the stages above it and the final SELECT sees them all. Option 0 is tempting because one WITH clause looks like a single shared namespace, but a reference to a name defined later cannot resolve; only WITH RECURSIVE allows a stage to refer to itself. Option 3 is also wrong: a stage can be read any number of times, as the main example shows by reading per_customer twice.