Multiple CTEs in a Single Query
What you'll learn: How to chain multiple CTEs together in one query to break down complex logic into clear, readable steps.
The Core Idea
Just as you can define one CTE before your main query, you can define multiple CTEs by separating them with commas. Each CTE can reference the ones defined before it, allowing you to build up your analysis in logical layers. Think of it like a recipe with multiple prep steps—first you chop vegetables, then you make the sauce, then you combine everything. Each step builds on the previous ones.
Basic Syntax Pattern
WITH
cte1 AS (
SELECT column1, column2
FROM table1
WHERE condition
),
cte2 AS (
SELECT column1, COUNT(*) as count
FROM cte1 -- Can reference cte1!
GROUP BY column1
),
cte3 AS (
SELECT *
FROM cte2
WHERE count > 10
)
SELECT *
FROM cte3;
Notice:
- Single
WITHkeyword at the start - Each CTE separated by a comma (not another
WITH) - Later CTEs can reference earlier ones
- Only the final
SELECTends with a semicolon
Why Use Multiple CTEs?
Instead of nesting subqueries deeply or writing one massive query, multiple CTEs let you:
- Break complex logic into named, understandable pieces
- Build calculations step-by-step
- Make debugging easier (you can test each CTE independently)
- Improve code readability for yourself and teammates
Example Mental Model
Think of it like variables in programming: you calculate total_sales, then use that to calculate sales_growth, then filter to high_growth_products—each step clearly labeled and building on the last.
Key Takeaway: Multiple CTEs transform complex queries into a readable pipeline of logical steps, with each CTE building on previous ones using comma-separated definitions after a single WITH keyword.