Chained CTEs in SQL
A chained CTE query defines multiple named subqueries in a single `WITH` clause, each available to the main query and to CTEs that follow it. The chain is a sequence of named intermediate layers, resolved in the order they are written.
Before this Common Table Expressions (CTEs)
Builds toward Recursive CTEs, Multi-CTE Query Architecture, Sessionization and Funnel Analysis Patterns
Chained CTEs let you break a complex query into named layers, each doing one thing, in the order you read them.
You're calculating average monthly spend per user plan type. You need to filter to active users, aggregate each user's charges, then join the two results and aggregate again. That's three layers of logic. Written as nested subqueries, the innermost layer executes first but appears deepest in the code — you read it inside-out. With chained CTEs, each layer gets a name and sits in reading order.
The WITH clause holds all the definitions, separated by commas. Think of each CTE as a named step. The main query at the bottom reads from whichever step produces the final result. Names flow forward — each CTE can reference any CTE defined before it in the list, but not after it.
WITH active_users AS (
SELECT user_id, plan
FROM users
WHERE status = 'active'
),
monthly_spend AS (
SELECT user_id, SUM(amount) AS total
FROM charges
GROUP BY user_id
),
summary AS (
SELECT a.plan, AVG(m.total) AS avg_spend
FROM active_users a
JOIN monthly_spend m ON a.user_id = m.user_id
GROUP BY a.plan
)
SELECT *
FROM summaryactive_users filters the users table. monthly_spend aggregates charges. summary joins the two and aggregates again. The main query reads from summary. Each layer is narrow enough to understand in isolation.
Order matters. If you put summary before monthly_spend, PostgreSQL errors — monthly_spend hasn't been defined yet. PostgreSQL reads the WITH clause top to bottom, and a name is only visible after it appears.
The debugging advantage is significant. When a chained CTE query returns wrong results, you don't have to guess which step failed. Comment out the main SELECT and add SELECT * FROM active_users instead. Run it. Inspect the intermediate result. Add the next CTE back and repeat. Each step is independently queryable.
When should you use chaining vs a single CTE? A single CTE makes sense when you have one intermediate result and one main query. Chaining makes sense when the query has multiple distinct stages, when the same intermediate result feeds into more than one downstream CTE, or when the logic is complex enough that naming each stage makes the overall structure easier to review. There is no rule that says a query must have a certain number of CTEs — use as many as the problem calls for, and no more.
The one thing that trips people up: chained CTEs do not run in parallel.
PostgreSQL executes the whole query as a single plan. CTEs in the chain that have no dependency on each other are still run sequentially. The chain is a logical structure for the analyst's benefit, not an instruction to the database to parallelize work. If you need two independent aggregations, PostgreSQL still processes them one at a time.
9 Chained CTEs practice problems
Write a query to return the category ID and average price for every category whose average product price exceeds `$300`.
Write a query to return the department ID and headcount for every large department.
Write a query to return the user ID and total session count for every user with at least one such session.
Write a query to return the user ID and average events per session for every highly engaged user.
Write a query to return the category ID and average price for every category whose average exceeds the cross-category average.
Write a query to return each qualifying customer's ID and their count of high-value orders.
Write a query to return each qualifying user's ID and their count of high-intensity sessions.
Write a query to return the count of top-engagement users and their average total event count as a single row.
Write a query to return the category ID and average price for every premium, well-stocked category.
These problems are part of the Chained CTEs lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.