Chaining CTEs
The full text of this chapter, to read or revisit. To practise, do the exercises.
βΆ Do the exercisesExercise 1 β Several CTEs
One WITH can define several CTEs, separated by commas. Badges and wins per trainer, side by side:
WITH badges_per_trainer AS (
SELECT trainer_id, COUNT(*) AS badges
FROM badges
GROUP BY trainer_id
),
wins_per_trainer AS (
SELECT winner_id, COUNT(*) AS wins
FROM battles
GROUP BY winner_id
)
SELECT t.name,
COALESCE(b.badges, 0) AS badges,
COALESCE(w.wins, 0) AS wins
FROM trainers t
LEFT JOIN badges_per_trainer b ON b.trainer_id = t.trainer_id
LEFT JOIN wins_per_trainer w ON w.winner_id = t.trainer_id
ORDER BY t.name;Each CTE has one row per trainer, so the joins cannot multiply anything β the same fan-out protection as derived tables, with names you can read.
Only one WITH
Write WITH once, then name AS (β¦) blocks separated by commas: WITH a AS (β¦), b AS (β¦) SELECT β¦. A second WITH is a syntax error.
Exercise 2 β A pipeline
A CTE can use the CTEs defined before it. That turns a complex query into a pipeline of small steps β here shares reuses per_trainer, and the main query only adds the names:
WITH per_trainer AS (
SELECT trainer_id, COUNT(*) AS badges
FROM badges
GROUP BY trainer_id
),
shares AS (
SELECT trainer_id, badges,
badges * 100 / (SELECT SUM(badges) FROM per_trainer) AS pct
FROM per_trainer
)
SELECT t.name, s.badges, s.pct
FROM shares s
INNER JOIN trainers t ON t.trainer_id = s.trainer_id
ORDER BY s.pct DESC;Read it top to bottom: count the badges, turn each count into a share of the total, add the names. Each step is small enough to check on its own β and Morgan holds half of all the badges.
Order matters
shares may use per_trainer because it comes after it. The other way round β using a CTE defined further down β is an error in most databases; SQLite happens to tolerate it, but always define before you use.
Exercise 3 β Who can see whom?
In WITH a AS (β¦), b AS (β¦), c AS (β¦) SELECT β¦, which CTEs can c use?
Hint : Top to bottom, like reading.
Exercise 4 β Zeros for Jordan
Complete the query so that every trainer appears with their badges and wins, 0 when they have none.
Hint : The wins live in the alias of the second CTE; the replacement value is a zero.
Exercise 5 β Check one step at a time
A chain of CTEs is easy to check step by step. Here is a two-step pipeline: the wins per trainer, then only the trainers with at least 2 wins:
WITH wins_per_trainer AS (
SELECT winner_id, COUNT(*) AS wins
FROM battles
GROUP BY winner_id
),
big_winners AS (
SELECT winner_id, wins
FROM wins_per_trainer
WHERE wins >= 2
)
SELECT t.name, b.wins
FROM big_winners b
INNER JOIN trainers t ON t.trainer_id = b.winner_id
ORDER BY b.wins DESC;Is step 1 right? Keep the WITH exactly as it is and replace only the final query with a SELECT * on the step you want to inspect:
WITH wins_per_trainer AS (
SELECT winner_id, COUNT(*) AS wins
FROM battles
GROUP BY winner_id
),
big_winners AS (
SELECT winner_id, wins
FROM wins_per_trainer
WHERE wins >= 2
)
SELECT * FROM wins_per_trainer
ORDER BY winner_id;Three winners: Alex with 1 win, Morgan with 3, Taylor with 2. Step 1 is correct, and Alex is rightly dropped by step 2 β put the real final query back and move on.
Notice that big_winners is defined but not used in that second run: an unused CTE is allowed, it is simply ignored. That is what makes the trick painless β you never have to delete anything.
Names are documentation
wins_per_trainer says what the rows are; t1 says nothing. Name CTEs by their content, and a pipeline explains itself.
Exercise 6 β The pipeline
Match each step of the badge-share pipeline with the block that does it.
per_trainershares(SELECT SUM(badges) FROM per_trainer)Exercise 7 β Spot the valid chain
Which statement is valid?
Hint : One WITH, each query in parentheses, commas between CTEs, and the main query follows directly.
Exercise 8 β League points
Professor Pi scores the season: 2 points per badge, 3 points per battle won.
Chain two CTEs β badges per trainer and wins per trainer β then return name, badges, wins and points for every trainer (0 when none), sorted by points descending then name.
-- Write your query below