Nesting Multiple CTEs (The Power of Chaining)

It's **Day 52**, and we're chaining. You aren't limited to just one CTE. You can have as many as you want, and each one can reference the ones above it!

The Pipeline Pattern

Think of multiple CTEs like an assembly line.

WITH raw_data AS (

SELECT id, total, user_id FROM orders

),

user_summary AS (

-- This CTE uses the one above it!

SELECT user_id, SUM(total) as lifetime_spent

FROM raw_data

GROUP BY user_id

),

high_value_users AS (

-- And this one uses 'user_summary'

SELECT * FROM user_summary

WHERE lifetime_spent > 10000

)

SELECT u.name, hvu.lifetime_spent

FROM users u

JOIN high_value_users hvu ON u.id = hvu.user_id;

Why this is powerful

You can debug each "step" of your query individually. If your final result is wrong, you can just change your final `SELECT` to `SELECT * FROM raw_data` to see where the data went off track.

The Rule

You only write the `WITH` keyword once. Use commas to separate your CTEs.

Your Task for Today

Write a 3-step pipeline using CTEs to clean, group, and then filter a dataset.

*Day 53: Recursive CTEs—The ultimate SQL brain teaser.*