Name the step with WITH

A common table expression (CTE) gives a subquery a name. WITH name AS (...) defines it, and the rest of the query uses the name like a table. You read the query top to bottom instead of from the innermost parentheses out.

Take a music service with artists, tracks and plays. Which artists are played more than the average artist?

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;

artist_plays is defined once and used twice. The nested-subquery version has to paste the three-table join twice.

Chaining

One WITH can hold several CTEs separated by commas. Each can read the ones before it, so a query becomes a pipeline: filter, aggregate, join back.

WITH late_june AS (
  SELECT * FROM plays WHERE played_at >= '2024-06-15'
),
track_counts AS (
  SELECT track_id, count(*) AS plays FROM late_june GROUP BY track_id
)
SELECT t.title, tc.plays
FROM track_counts tc JOIN tracks t ON t.id = tc.track_id
ORDER BY tc.plays DESC, t.title;

To debug, replace the final SELECT with SELECT * FROM track_counts and look at the middle step.

Materialization

Since PostgreSQL 12 a CTE that is read once and has no side effects is inlined into the outer query. Write AS MATERIALIZED to force it to run once, or AS NOT MATERIALIZED to force inlining.