SQL Problem Solving: Reading Complex Queries

Concept. Read a query in execution order, not written order: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. Each clause hands its rows to the next.

Intuition. To see what a six-clause query returns, trace it one clause at a time over the Listens rows, watching the row set shrink and reshape at each step.

Example 1: Finding Top Genres Per User

-- Goal: For each user, find genres they have 2+ songs in.
-- Read in execution order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

SELECT user_id, genre,                    -- Step 5: project columns
       COUNT(*)    AS song_count,
       AVG(rating) AS avg_rating
FROM Listens l                            -- Step 1a: read Listens
JOIN Songs   s ON l.song_id = s.song_id   -- Step 1b: join Songs
WHERE genre IS NOT NULL                   -- Step 2: drop NULL genres
GROUP BY user_id, genre                   -- Step 3: form groups
HAVING COUNT(*) >= 2                      -- Step 4: keep 2+ songs
ORDER BY user_id, avg_rating DESC;        -- Step 6: sort output

The Mental Model

Example 1 traced on one page. FROM and JOIN give 9 rows, each play with its genre. GROUP BY collapses them to 6 user-genre groups with counts. HAVING COUNT(*) >= 2 strikes the three groups with a count of 1, leaving three pairs: Mickey-Pop, Minnie-Classic, Daffy-Pop.

Example 2: Finding Power Users

The Query

-- Goal: Find the top-half users by average rating.
-- Three layers: per-user metrics (CTE 1), percentile rank (CTE 2), final filter.

WITH user_stats AS (                                -- CTE 1: per-user metrics
SELECT user_id,
       COUNT(*)    AS listen_count,
       AVG(rating) AS avg_rating
FROM Listens
GROUP BY user_id
),
ranked_users AS (                                   -- CTE 2: percentile rank
SELECT user_id, listen_count, avg_rating,
       PERCENT_RANK() OVER (ORDER BY avg_rating DESC) AS rating_rank
FROM user_stats
)
SELECT u.name,                                      -- final layer: name + filter
     r.listen_count,
     r.avg_rating
FROM ranked_users r
JOIN Users u ON r.user_id = u.user_id
WHERE r.rating_rank < 0.5                           -- top half
ORDER BY r.avg_rating DESC;

How to Read This Query