WITH basics
The full text of this chapter, to read or revisit. To practise, do the exercises.
βΆ Do the exercisesExercise 1 β Name a query
Thanks to subqueries, you already know how to count badges per trainer with a derived table β a query tucked inside the FROM:
SELECT t.name, COALESCE(p.badge_count, 0) AS badges
FROM trainers t
LEFT JOIN (SELECT trainer_id, COUNT(*) AS badge_count
FROM badges
GROUP BY trainer_id) AS p ON p.trainer_id = t.trainer_id
ORDER BY t.trainer_id;A CTE β Common Table Expression β does exactly the same thing, but the inner query is written first and given a name with WITH. The main query then reads like a sentence:
WITH per_trainer AS (
SELECT trainer_id, COUNT(*) AS badge_count
FROM badges
GROUP BY trainer_id
)
SELECT t.name, COALESCE(p.badge_count, 0) AS badges
FROM trainers t
LEFT JOIN per_trainer p ON p.trainer_id = t.trainer_id
ORDER BY t.trainer_id;WITH per_trainer AS (β¦) defines a temporary, named result. It exists only for this statement, and the main query uses per_trainer like any table.
Same engine, different reading order
Nothing changes for the database β a CTE and a derived table produce the same plan. The difference is for humans: the building blocks come first, the conclusion last, and nothing is nested three levels deep.
Exercise 2 β One name, used twice
Once named, a CTE can be used several times in the same statement β something a derived table cannot do without being copied. Each trainer's badge count next to the record:
WITH per_trainer AS (
SELECT trainer_id, COUNT(*) AS badge_count
FROM badges
GROUP BY trainer_id
)
SELECT t.name, p.badge_count,
(SELECT MAX(badge_count) FROM per_trainer) - p.badge_count AS gap_to_record
FROM per_trainer p
INNER JOIN trainers t ON t.trainer_id = p.trainer_id
ORDER BY gap_to_record;per_trainer appears in the FROM and again inside the scalar subquery; it is computed once.
You can also rename the columns of a CTE by listing them after its name:
WITH strong(codemon_name, lvl) AS (
SELECT name, level FROM codemon WHERE level >= 12
)
SELECT * FROM strong ORDER BY lvl DESC;Exercise 3 β Where does WITH go?
Where is a CTE written?
Hint : WITH name AS (β¦) SELECT β¦
Exercise 4 β The strong ones
Complete the query so that it names the CodΓ©mons of level 12 or more strong, then lists their names, strongest first.
Hint : The keyword that opens a CTE, then its name in the FROM.
Exercise 5 β Aggregating an aggregate, readably
Averaging a count needs two levels. With a CTE, the two levels are two readable blocks:
WITH per_trainer AS (
SELECT trainer_id, COUNT(*) AS badge_count
FROM badges
GROUP BY trainer_id
)
SELECT ROUND(AVG(badge_count), 2) AS avg_badges,
MAX(badge_count) AS record
FROM per_trainer;First block: badges per trainer (3, 5, 2). Second block: their average and their maximum.
A CTE is not a table
It vanishes as soon as the statement ends β the next query cannot see per_trainer. To keep a result, use CREATE TABLE β¦ AS SELECT.
Exercise 6 β Parts of a CTE
Match each element with its role.
WITH per_trainerAS (SELECT β¦)SELECT β¦ FROM per_trainerExercise 7 β How long does it live?
How long does a CTE exist?
Hint : Temporary and nameless outside its statement.
Exercise 8 β The busiest gym
Complete the query so that it returns the gym that handed out the most badges, using the CTE twice.
Hint : Read from the CTE, and compare with its highest count.
Exercise 9 β Winning more than average
Professor Pi wants the trainers who win at least as often as the average winner.
Write a CTE wins_per_trainer (winner_id, wins), then return name and wins of the trainers whose wins are at least the average of wins in that CTE. Sort by wins descending.
-- Write your query below