Referencing Earlier CTEs in Later Ones
You'll learn how to build a chain of transformations by having later CTEs reference earlier ones, creating a clear step-by-step data pipeline.
The Sequential CTE Pattern
Once you've defined a CTE, it becomes available for use not just in the final query, but also in subsequent CTEs within the same WITH clause. This lets you break complex logic into manageable steps, where each CTE refines or extends the work of the previous one.
Think of it like cooking: your first CTE might chop vegetables, your second CTE cooks them, and your third CTE adds seasoning. Each step builds on the last.
How It Works
When you write multiple CTEs separated by commas, they execute in order. Any CTE can reference any CTE defined before it:
WITH step1 AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
),
step2 AS (
SELECT customer_id, total_spent
FROM step1
WHERE total_spent > 1000
),
step3 AS (
SELECT COUNT(*) AS high_value_customers
FROM step2
)
SELECT * FROM step3;
Here, step2 references step1, and step3 references step2. Each CTE adds another layer of filtering or calculation.
Why This Matters
This sequential approach makes complex queries readable and maintainable. Instead of deeply nested subqueries, you get a clear flow: raw data → filtered data → aggregated data → final result. Each transformation has a meaningful name and purpose.
Key Takeaway: Later CTEs can reference earlier CTEs in the same WITH clause, enabling you to build multi-step transformations that are both powerful and easy to understand.