WITH basics

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

β–Ά Do the exercises

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

Options
β€’ Before the main query, which starts after the closing parenthesis
β€’ Inside the FROM clause, in parentheses
β€’ After the main query, like ORDER BY
β€’ In a separate statement, run before the query

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.

Items to match
β€’ WITH per_trainer
β€’ AS (SELECT …)
β€’ SELECT … FROM per_trainer
β€’ A subquery inside FROM
Definitions
β€’ Opens the CTE and names it
β€’ The query that fills it
β€’ The main query, which uses it like a table
β€’ The same idea without a name: a derived table

Exercise 7 β€” How long does it live?

How long does a CTE exist?

Options
β€’ For the duration of the single statement that defines it
β€’ Until the end of the session
β€’ Forever, like a table
β€’ Until it is dropped with DROP CTE

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.

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