CTEs: Common Table Expressions

Core Concept

• Example: Break complex queries into named steps (WITH HighRatedSongs AS ... transforms nested chaos into readable flow)

Goal: Find all users who listen to Rock genre songs.

Why CTEs?

• Like functions in python/java - named, reusable computations within a query

• Linear readability - read top-to-bottom instead of inside-out nesting

• Debug intermediate steps - test each CTE independently

• Reusable - reference the same CTE multiple times

• Recursion support for hierarchical data (unique to CTEs)

CTE vs Subquery Comparison

-- Find users who listen to Rock genre songs (read inside-out)
SELECT name FROM Users
WHERE user_id IN (
    -- Step 2 (middle): Find users who listened to Rock songs
    SELECT user_id FROM Listens
    WHERE song_id IN (
        -- Step 1 (innermost): Find all Rock songs first
        SELECT song_id FROM Songs
        WHERE genre = 'Rock'
    )
)
-- Step 3 (outer): Get names of those users

-- Find users who listen to Rock genre songs (step-by-step clarity)
WITH RockSongs AS (
    -- Step 1: Find all Rock genre songs
    SELECT song_id FROM Songs
    WHERE genre = 'Rock'
),
RockListeners AS (
    -- Step 2: Find users who listened to those songs
    SELECT DISTINCT user_id FROM Listens
    WHERE song_id IN (SELECT song_id FROM RockSongs)
)
-- Step 3: Get names of those users
SELECT name FROM Users
WHERE user_id IN (SELECT user_id FROM RockListeners)

Simple CTE: HighRatedSongs

-- Find songs with average rating above 4.0
┌─────────────────────────────────────────────────────┐
│ WITH HighRatedSongs AS (                            │
│     -- Calculate average rating per song            │
│     SELECT song_id, AVG(rating) AS avg_rating       │
│     FROM Listens                                    │
│     GROUP BY song_id                                │
│     HAVING AVG(rating) > 4.0                        │
│ )                                                   │
└─────────────────────────────────────────────────────┘
-- Join with Songs table to get titles
SELECT s.title, h.avg_rating
FROM HighRatedSongs h                -- Use CTE
JOIN Songs s ON h.song_id = s.song_id

• CTE calculates averages first, then filters

• Only songs 2 and 7 pass the >4.0 threshold

• Main query joins CTE with Songs for titles

Multiple CTEs: User Statistics

-- Calculate multiple statistics per user
┌─────────────────────────────────────────────────────┐
│ WITH UserListenCounts AS (                          │
│     -- Count total listens per user                 │
│     SELECT user_id, COUNT(*) AS listen_count        │
│     FROM Listens                                    │
│     GROUP BY user_id                                │
│ ),                                                  │
│ UserHighRatings AS (                                │
│     -- Average of only high ratings (4+) per user   │
│     SELECT user_id,                                 │
│            AVG(rating) AS high_avg,                 │
│            COUNT(*) AS high_count                   │
│     FROM Listens                                    │
│     WHERE rating >= 4.0  -- Only consider 4+ stars │
│     GROUP BY user_id                                │
│ )                                                   │
└─────────────────────────────────────────────────────┘
-- Combine both CTEs with Users table
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

Note: These could be combined using CASE statements (e.g., COUNT(CASE WHEN rating >= 4.0 THEN 1 END)), but separating them is clearer: each CTE has one purpose, filtering logic is obvious, and it demonstrates how multiple CTEs work together.

• Two CTEs with different purposes: all listens vs high ratings only

• UserHighRatings filters to ratings >= 4.0 before aggregating

• Main query combines both CTEs with LEFT JOINs

• Pluto gets NULL values (no listens to count)

Common Patterns

• CTEs are computed once and reused (unlike subqueries)

• Join conditions on CTE results should be indexed in source tables

• Complex CTEs may be materialized for better performance