AI Hints
Homeโ€บSQL Tutorialโ€บCTEs, EXISTS, and Set Operations
SQL ยท Lesson 59 of 126

Chaining CTEs

Step by step analysis.

โฑ 2 min readโ–ถ Video at 1:24:24๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Chaining CTEs
From the video: Chaining CTEs
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

Small stepseach easy to check
Build upone step at a time
Clear namesspend, tiers
Easy to fixfind the broken step

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
Next up: EXISTS โ€” is there at least one?