Chaining CTEs
Step by step analysis.
Helen wants a loyalty programme, with three tiers. Gold for customers who've spent two hundred dollars or more. Silver for one hundred or more. Bronze for everyone else. How many customers are in each tier? That's three steps. Work out spending, give each customer a tier, then count.

- A loyalty programme
- Gold, Silver, Bronze
- Three clear steps
Three steps, three names
Separate several CTEs with commas. Step one, spend, adds up what each customer has spent. Step two, tiers, uses spend, and gives each customer a tier with case. Step three, the main query, counts each tier. Run it. Fifty gold customers, one hundred and ten silver, and two hundred and ninety bronze.
WITH spend AS (
SELECT o.customer_id,
SUM(oi.quantity * b.price) AS spent
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY o.customer_id
),
tiers AS (
SELECT CASE WHEN spent >= 200 THEN 'Gold'
WHEN spent >= 100 THEN 'Silver'
ELSE 'Bronze' END AS tier
FROM spend
)
SELECT tier, COUNT(*) AS customers
FROM tiers
GROUP BY tier
ORDER BY customers;โโโโโโโโโโฌโโโโโโโโโโโโ โ tier โ customers โ โโโโโโโโโโผโโโโโโโโโโโโค โ Gold โ 50 โ โ Silver โ 110 โ โ Bronze โ 290 โ โโโโโโโโโโดโโโโโโโโโโโโ
Why analysts love this
This is how real analysts work. Each step is small, and easy to check on its own. You can run just the first CTE, look at the result, then add the next step. If something's wrong, you know exactly which step to look at.
Summary
Chain CTEs with commas, and each one can use the ones before it. Next, a quick way to ask, does a matching row exist?
Key takeaways- Separate with commas
- Later steps use earlier ones
- Build and check