CTEs: Common Table Expressions
Concept. A CTE (WITH name AS (...)) names an intermediate query result so the rest of the query can reference it like a table.
Intuition. Find the users who listen to Classic by naming the steps: a ClassicSongs CTE lists the Classic song ids, a ClassicListeners CTE finds who played them, then the final query reads their names. Each step is a named table you reference, instead of nesting one subquery inside another.
Figure: CTEs: name the steps of a queryA before-and-after. The left panel (red, the problem) shows three subqueries nested inside one another, read inside-out and hard to debug. The right panel (green, the better way) shows the same logic as three named CTE steps read top to bottom: ClassicSongs returns {6,7,8}, ClassicListeners returns {1,2,3}, the final query returns {Mickey, Minnie, Daffy}. Color key: red is the hard-to-read nested way, green is the clear CTE way, grey is a neutral step label, and the ink target marks the takeaway.
Figure 1. The same query, written with nested subqueries or named CTEs. Read the nested form inside out and the CTE definitions top to bottom. Both return Mickey, Minnie, and Daffy.
Simple CTE: HighRatedSongs
-- Step 1: WITH names a result you can reuse
WITH HighRatedSongs AS (
SELECT song_id, AVG(rating) AS avg_rating
FROM Listens
GROUP BY song_id
HAVING AVG(rating) > 4.0
)
-- Step 2: the outer query reads it by name
SELECT s.title, h.avg_rating
FROM HighRatedSongs h
JOIN Songs s ON h.song_id = s.song_id
HighRatedSongs
| song_id | avg_rating |
|---|---|
| 2 | 4.6 |
| 7 | 4.6 |
Final Output
| title | avg_rating |
|---|---|
| Willow | 4.6 |
| Yellow Submarine | 4.6 |
The CTE keeps the two songs averaging above 4.0. Joining to Songs adds their titles.
Song 2 averages (4.2 + 4.7 + 4.9) / 3 = 4.6; song 7 has one rating, 4.6.
Multiple CTEs: User Statistics
-- Step 1: count every user's listens
WITH UserListenCounts AS (
SELECT user_id, COUNT(*) AS listen_count
FROM Listens
GROUP BY user_id
),
-- Step 2: average only their 4+ ratings
UserHighRatings AS (
SELECT user_id, AVG(rating) AS high_avg, COUNT(*) AS high_count
FROM Listens
WHERE rating >= 4.0
GROUP BY user_id
)
-- Step 3: join both CTEs back to Users
SELECT u.name, lc.listen_count, hr.high_avg, hr.high_count
FROM Users u
LEFT JOIN UserListenCounts lc ON u.user_id = lc.user_id
LEFT JOIN UserHighRatings hr ON u.user_id = hr.user_id
Conditional aggregation, such as COUNT(CASE WHEN rating >= 4.0 THEN 1 END), can combine this work in one aggregate query. Here, separate CTEs make the two inputs visible.
UserListenCounts: all listens
| user_id | listen_count | |
|---|---|---|
| 1 | 3 | |
| 2 | 3 | |
| 3 | 3 |
UserHighRatings: ratings ≥ 4.0
| user_id | high_avg | high_count | |
|---|---|---|---|
| 1 | 4.35 | 2 | |
| 2 | 4.65 | 2 | |
| 3 | 4.9 | 1 |
UserHighRatings filters before averaging. Pluto has no listens, so neither CTE contains his user ID.
The high-rating averages are (4.5 + 4.2) / 2 = 4.35 for Mickey, (4.7 + 4.6) / 2 = 4.65 for Minnie, and 4.9 for Daffy.
Final Output
| name | listen_count | high_avg | high_count | |
|---|---|---|---|---|
| Mickey | 3 | 4.35 | 2 | |
| Minnie | 3 | 4.65 | 2 | |
| Daffy | 3 | 4.9 | 1 | |
| Pluto | NULL | NULL | NULL |
Both LEFT JOINs preserve every row of Users. Pluto has no matching CTE row, so the joined columns are NULL, including listen_count.
CTE Names
Why databases made CTEs first-class
-
Readable: each step is a named block, read top to bottom instead of inside-out.
-
Recursion: a CTE can reference itself, to walk org charts, trees, and dependency graphs.
We'll discuss query performance in later modules.
Takeaway
Give each CTE an intermediate result you can describe, then combine those results in the outer query.