Chaining CTEs

The full text of this chapter, to read or revisit. To practise, do the exercises.

β–Ά Do the exercises

Exercise 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?

Options
β€’ a and b: a CTE can use the ones defined before it
β€’ b only, the one written just before it
β€’ a only: a CTE sees just the first one
β€’ None: CTEs are independent

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.

Items to match
β€’ Count the badges of each trainer
β€’ Turn each count into a share of the total
β€’ Look up the total of all badges
β€’ Add the names and sort
Definitions
β€’ per_trainer
β€’ shares
β€’ (SELECT SUM(badges) FROM per_trainer)
β€’ The final SELECT

Exercise 7 β€” Spot the valid chain

Which statement is valid?

Options
β€’ WITH a AS (SELECT 1 AS x), b AS (SELECT x + 1 AS y FROM a) SELECT y FROM b;
β€’ WITH a AS SELECT 1 AS x, b AS (SELECT x + 1 AS y FROM a) SELECT y FROM b;
β€’ WITH a AS (SELECT 1 AS x) WITH b AS (SELECT x + 1 AS y FROM a) SELECT y FROM b;
β€’ WITH a AS (SELECT 1 AS x), b AS (SELECT x + 1 AS y FROM a); SELECT y FROM b;

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.

Starter code
-- Write your query below
Back to the course