In the subqueries lesson you nested a SELECT inside a FROM clause and it worked — but you also had to read it inside-out. A common table expression (CTE) fixes that: WITH name AS (...) gives a subquery a name, and the rest of the query uses that name like a table. Same plan, far better prose.
The seed is a tiny music-streaming service: artists have tracks, and every listen lands in plays with a timestamp.
SELECT a.name, a.genre, t.title
FROM artists a
JOIN tracks t ON t.artist_id = a.id
ORDER BY a.id, t.id;
The readability problem
Question: which artists are played more than the average artist? Written with nothing but subqueries, the same per-artist rollup gets pasted in twice and you read it from the innermost parentheses outward:
SELECT name, total_plays
FROM (
SELECT a.name, count(*) AS total_plays
FROM artists a
JOIN tracks t ON t.artist_id = a.id
JOIN plays p ON p.track_id = t.id
GROUP BY a.name
) AS per_artist
WHERE total_plays > (
SELECT avg(plays)
FROM (
SELECT count(*) AS plays
FROM artists a
JOIN tracks t ON t.artist_id = a.id
JOIN plays p ON p.track_id = t.id
GROUP BY a.id
) AS inner_counts
)
ORDER BY total_plays DESC;
It works, but the interesting logic — "compare each artist to the average" — is buried under two copies of the same three-table join. Change the rollup and you must remember to change it in both places.
Name the step with WITH
Here is the same query as a CTE. Define the rollup once, give it a name, and read top to bottom:
WITH artist_plays AS (
SELECT a.name, count(*) AS total_plays
FROM artists a
JOIN tracks t ON t.artist_id = a.id
JOIN plays p ON p.track_id = t.id
GROUP BY a.name
)
SELECT name, total_plays
FROM artist_plays
WHERE total_plays > (SELECT avg(total_plays) FROM artist_plays)
ORDER BY total_plays DESC;
Two things happened. The query now reads like a recipe — "first compute artist_plays, then keep the above-average ones". And notice artist_plays appears twice in the main query: once in FROM, once inside the scalar subquery that computes the average. Define once, reference as often as you like — the duplication from the nested version is gone.