SQL Cheat Sheet

Concept. The Spotify tables, the order a query runs in, and the traps in NULL, GROUP BY, sets, joins, CTEs, window functions and subqueries, on one page.

Intuition. Every number on this page comes from running its query in Postgres on the 23 rows below. Press r on any SQL page to open this sheet.

The Three Tables

The Spotify schema: Users, Songs, and Listens shown together with all 23 rows. Users has primary key user_id; Songs has primary key song_id; Listens has primary key listen_id and two foreign keys, user_id pointing at Users.user_id and song_id at Songs.song_id.

Figure 1. All 23 rows: 4 users, 10 songs, 9 listens. Each Listens row links one user to one song by ID.

A tag marks a result that depends on one of these edge cases: A Pluto has no listens B DJ Mix has a NULL genre C Listen 9 has a NULL rating

The Order a Query Runs In

You write SELECT first. It runs fifth. The numbers give the run order, and the right column shows what is left after each step.

5SELECT u.name, AVG(l.rating) AS avg_rating2 rows

1FROM users u JOIN listens l ON u.user_id = l.user_id9 rows

2WHERE l.rating > 37 rows

3GROUP BY u.user_id, u.name3 groups

4HAVING COUNT(*) >= 22 groups

6ORDER BY avg_rating DESCMinnie 4.4, Mickey 4.2

7LIMIT 1Minnie 4.4

WHERE drops rows at 2: Daffy's 2.9 and his NULL rating. HAVING drops groups at 4: Daffy, who has one listen left. The alias avg_rating is set at 5, so ORDER BY at 6 can sort by it. WHERE and HAVING run earlier and cannot use it: Postgres reports column "avg_rating" does not exist. Window functions are also computed at 5, so WHERE cannot filter on them. The first window card below shows the fix.

NULL, Counts, Sets and Joins

NULL in WHERE, on Listens

rating = NULL0 rows

rating IS NULL1 rowC

rating > 45 rows

NOT (rating > 4)3 rowsC

Counts on Listens

COUNT(*)9

COUNT(rating)8C

AVG(rating)4.2, divides by 8C

genre on Songs

SELECT genre10 rows

SELECT DISTINCT genre4 rowsB

GROUP BY genre4 groupsB

COUNT(genre)9B

COUNT(DISTINCT genre)3B

Taylor Swift vs Ed Sheeran genres

UNIONPop, Rock

UNION ALL6 rows

INTERSECTRock

EXCEPTPop

Users with Listens

users JOIN listens9 rows, 3 usersA

users LEFT JOIN listens10 rowsA

Pluto's COUNT(l.listen_id)0A

Pluto's COUNT(*)1A

A comparison with NULL is unknown, and WHERE keeps only true, so listen 9 is in neither rating > 4 nor NOT (rating > 4). DISTINCT and GROUP BY treat NULL as one value, while COUNT skips it. UNION and INTERSECT and EXCEPT remove duplicates; UNION ALL keeps them. After the LEFT JOIN, grouped per user, Pluto is one row with NULL listen columns: COUNT(*) counts that row, and COUNT(l.listen_id) skips the NULL.

Traps in GROUP BY, CTEs, Windows and Subqueries

Each card shows a query, what Postgres returns, and the fix.

GROUP BY and CTEs

SELECT user_id, song_id, COUNT(*)
FROM listens
GROUP BY user_id;

ERROR: column "listens.song_id" must appear in the GROUP BY clause or be used in an aggregate function

Every SELECT column must be in GROUP BY or inside an aggregate. Add song_id to GROUP BY, or drop it.

WITH per_user AS (
  SELECT user_id, COUNT(*) AS n
  FROM listens GROUP BY user_id)
SELECT u.name, p.n
FROM users u JOIN per_user p
  ON u.user_id = p.user_id;

3 rows. Pluto is missing. A

Use LEFT JOIN. Pluto comes back with n = NULL, since per_user has no row for him. For a 0, count l.listen_id after a LEFT JOIN, as in the rows above.

Window

SELECT * FROM listens
WHERE RANK() OVER (ORDER BY rating DESC) = 1;

ERROR: window functions are not allowed in WHERE

Rank inside a CTE with RANK() OVER (ORDER BY rating DESC NULLS LAST) AS rnk, then select from the CTE WHERE rnk = 1. That returns listen 8.

SELECT user_id,
  AVG(rating) OVER (PARTITION BY user_id)
FROM listens;

9 rows, one per listen

A window keeps every row and puts the user's average on each. GROUP BY user_id returns 3.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY rating DESC)

Daffy's rank 1 is listen 9, rating NULL C

NULL sorts first here. Add NULLS LAST and rank 1 is listen 8, rating 4.9.

Subquery

-- listens with a rating Daffy never gave
SELECT listen_id FROM listens
WHERE rating NOT IN (
  SELECT rating FROM listens
  WHERE user_id = 3);

0 rows C

Daffy's ratings are 2.9, 4.9 and NULL, and NOT IN against a list that holds NULL is never true. Add AND rating IS NOT NULL inside the subquery to get 6 rows.

-- songs Mickey played
SELECT title FROM songs
WHERE song_id = (
  SELECT song_id FROM listens
  WHERE user_id = 1);

ERROR: more than one row returned by a subquery used as an expression

= compares with one value, and Mickey has 3 listens. Use IN.

-- users with a rating above 4.5
SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM listens l
  WHERE l.user_id = u.user_id
    AND l.rating > 4.5);

Minnie, Daffy. NOT EXISTS: Mickey, Pluto. A

Mickey's best is exactly 4.5, which is not above 4.5. Pluto has no listens, so nothing exists for him and NOT EXISTS is true.