Figure: The Spotify schema: Users, Songs, and Listens, joined by two foreign keys

Three tables shown together. Users has a primary key user_id; Songs has a primary key song_id; Listens has a primary key listen_id and two foreign keys, user_id pointing at Users.user_id and song_id pointing at Songs.song_id. One row is decoded first: listen 8 means Daffy (user 3) played Willow (song 2) and rated it 4.9. Then two links are drawn value to value: a box round the 3 in that row's user_id, an arrow to a box round the 3 in Users, and a box round the 2 in its song_id, an arrow to a box round the 2 in Songs. Color key: blue PK and FK badges mark the keys, per-user identity bars color the rows by user.

Figure: Reading SQL DDL: how the Listens table is defined

The CREATE TABLE listens statement, annotated. Each column names a type: INT for the integer ids, DECIMAL(2,1) for a rating like 4.5, TIMESTAMP for the time. PRIMARY KEY marks listen_id as the unique row id; NOT NULL marks a column required, and rating and listen_time omit it so they may be NULL. Two FOREIGN KEY ... REFERENCES clauses tie user_id to a real Users row and song_id to a real Songs row, so the database rejects a listen that points at a user or song that does not exist. Blue annotations in the margin label the type, the primary key, and the foreign keys.

Figure: SELECT-FROM-WHERE keeps some rows, then some columns

FROM Listens loads nine rows with columns listen_id, user_id, song_id, rating, colored by user. WHERE rating greater than 4.0 strikes the four failing rows (3.9, 3.9, 2.9, and the NULL which is UNKNOWN). SELECT user_id, song_id, rating projects the five survivors to three columns, dropping listen_id. Color key: Mickey blue, Minnie orange, Daffy purple; struck rows fail WHERE.

Figure: AND keeps a row only when both conditions hold; OR when either does

Two queries side by side on the same nine rows. Left: WHERE user_id = 1 AND rating > 4.0 keeps only rows where both are true, two rows (Mickey 4.5 and 4.2); the rest are struck out. Right: WHERE user_id = 1 OR rating > 4.0 keeps a row when either is true, six rows. The NULL rating makes rating > 4.0 UNKNOWN, so that row is dropped by both. Color key: Mickey blue, Minnie orange, Daffy purple; green check keeps, grey strike drops.

Figure: An alias renames the output column, but WHERE cannot use it

AS renames a column in the result: rating * 2 AS double_rating, so 4.5 shows as 9.0. Then the same filter written two ways: WHERE double_rating > 8 fails, because no column of that name exists yet, and WHERE rating * 2 > 8 works, because the expression already exists. Last, the reason: execution order runs FROM, then WHERE, then SELECT, and the alias is created in SELECT, after WHERE has finished. Color key: green is the alias and the working query, red is the failing reference.

Figure: NULL adds a third truth value, UNKNOWN

Binary logic: 4.5 > 4.0 is TRUE, 4.5 > 5.0 is FALSE. But any comparison with NULL (4.5 > NULL, NULL = NULL, rating = NULL) is UNKNOWN, the third truth value, shown grey with a question mark. WHERE keeps only TRUE rows. FALSE and UNKNOWN rows drop silently; rating IS NULL is the test that returns a real TRUE or FALSE and finds NULL rows. Color key: green TRUE, red FALSE, grey question mark UNKNOWN.

Figure: Two-valued boolean logic: AND, OR, NOT truth tables

Two-valued boolean logic: AND, OR, NOT truth tables Color key: green TRUE, red FALSE, grey UNKNOWN/NULL.

Figure: How AND, OR, and NOT handle UNKNOWN

FALSE AND UNKNOWN is FALSE; TRUE AND UNKNOWN stays UNKNOWN. TRUE OR UNKNOWN is TRUE; FALSE OR UNKNOWN stays UNKNOWN. NOT UNKNOWN stays UNKNOWN. Use rating IS NULL to test whether a value is missing.

Figure: AND keeps one row; OR keeps seven rows, including the NULL row

All nine Listens rows pass through user_id = 3 AND rating > 4 and through the OR version. The AND predicate keeps only listen 8; listen 9 becomes UNKNOWN and drops. The OR predicate keeps seven rows, including listen 9 because user_id = 3 makes the whole predicate TRUE. Color key: purple bars mark Daffy's rows; strikethrough marks rows WHERE drops; green is TRUE, red is FALSE, grey is UNKNOWN.

Figure: The same NULL row across three WHERE filters

The same NULL row across three WHERE filters Color key: green TRUE, red FALSE, grey UNKNOWN/NULL.

Figure: COUNT(*)=9 counts all; COUNT(rating)=8 and AVG=4.2 skip the NULL

COUNT(*)=9 counts all; COUNT(rating)=8 and AVG=4.2 skip the NULL Color key: green TRUE, red FALSE, grey NULL/UNKNOWN.

Figure: GROUP BY collapses a nine-row Listens table into a three-row grouped table

A nine-row Listens table (user_id, song_id, rating), colour-barred by user, is grouped by user_id into a three-row table of user_id, COUNT and AVG.

Figure: GROUP BY user_id: nine listens collapse into three groups, one row each

GROUP BY user_id collapses nine Listens rows into three groups, one per user, each with COUNT of three. Pluto has no listens, so Pluto gets no group and no output row. Color key: per-user identity bars (Mickey blue, Minnie orange, Daffy purple, Pluto cyan).

Figure: GROUP BY user_id with four aggregates per group: COUNT, AVG, MAX, MIN

Nine listens grouped by user into three rows, each carrying COUNT, AVG, MAX and MIN of the ratings. COUNT counts the rows; AVG, MAX and MIN ignore NULL ratings.

Figure: Multi-column GROUP BY splits one group into finer groups

By user_id, Mickey is one group of three rows with COUNT three. Adding song_id splits it into three groups of one row each, COUNT one.

Figure: A grouped row cannot show a column you did not group by

GROUP BY user_id collapses each user's listens to a single row. The query also asks for song_id, which was not grouped, so it shows a grey question mark: Mickey's row collapsed songs 1, 2 and 6, and the database cannot pick one. Margin notes list the candidate songs. Color key: grey question mark marks the unresolved column, per-user identity bars color the rows.

Figure: HAVING filters groups by their AVG(rating)

An average rating filter on groups: HAVING tests each group's AVG(rating); Mickey 4.2 and Minnie 4.4 pass, Daffy 3.9 is below 4.0 and the whole group drops. Color key: Mickey blue, Minnie orange, Daffy purple; struck rows/groups are filtered out.

Figure: HAVING COUNT(*) >= 2 on the groups from GROUP BY song_id

A dark SQL card above the five groups GROUP BY song_id produces, drawn as a table of song_id, COUNT(*) and AVG(rating). To the right of each row a margin note gives the HAVING test that row faces, and every row that fails is struck through. A line under the table gives the output. Color key: a green margin note keeps the group, a struck row is filtered out.

Figure: HAVING with AND: a group must pass both conditions

A dark SQL card above the three groups GROUP BY user_id produces, drawn as a table of user_id, COUNT(*), AVG(rating) and MIN(rating), one row per user with a colored identity bar. To the right of each row a margin note gives the verdict from the two conditions, and the row that fails is struck through. A line under the table gives the output. Color key: Mickey blue, Minnie orange, Daffy purple; a struck row is filtered out.

Figure: WHERE filters rows, then HAVING filters groups

WHERE rating > 3.5 strikes Daffy's 2.9 and NULL first; the survivors group into Mickey 3 rows (AVG 4.2), Minnie 3 (AVG 4.4), Daffy 1 (AVG 4.9); HAVING COUNT(*) >= 2 then drops Daffy's one-row group, leaving users 1 and 2. Color key: Mickey blue, Minnie orange, Daffy purple; struck rows/groups are filtered out.

Figure: The four set operations as row tables

Two query results: A is Mickey and Minnie, B is Mickey and Daffy; Mickey (teal) is in both, Minnie (blue) only in A, Daffy (violet) only in B. UNION returns the three distinct users; UNION ALL keeps the duplicate Mickey for four rows; INTERSECT returns only Mickey (in both); EXCEPT (A minus B) returns only Minnie. Color key: teal in both, blue only A, violet only B, amber the kept duplicate.

Figure: Users joined to Listens on user_id

A Users table and a Listens table are matched on user_id. Mickey, Minnie and Daffy each pair with three listens, giving nine inner-join rows. Pluto has no listens, so the inner join drops him.

-- Keep matched pairs; users without listens drop.
SELECT u.user_id, u.name, l.song_id, l.rating
FROM Users u
INNER JOIN Listens l ON u.user_id = l.user_id
ORDER BY u.user_id

Figure: Users joined to Listens on user_id; LEFT JOIN keeps the unmatched Pluto

A Users table and a Listens table are matched on user_id. Mickey, Minnie and Daffy each pair with three listens, giving nine inner-join rows. Pluto has no listens and no match: INNER JOIN drops him, while LEFT JOIN keeps him as a tenth row with NULL song_id and rating.

-- Keep every user; missing listen columns are NULL.
SELECT u.user_id, u.name, l.song_id, l.rating
FROM Users u
LEFT JOIN Listens l ON u.user_id = l.user_id
ORDER BY u.user_id

Figure: Listens RIGHT JOIN Songs keeps the five unplayed songs

Listens on the left and Songs on the right are matched on song_id. Five songs (Shape of You, Photograph, Shivers, Bad Blood, DJ Mix) were never played. INNER JOIN returns the nine played-song rows; RIGHT JOIN keeps all ten songs, adding the five unplayed ones NULL-filled, for fourteen rows.

-- Keep every song; missing listen columns are NULL.
SELECT s.song_id, s.title, l.rating
FROM Listens l
RIGHT JOIN Songs s ON l.song_id = s.song_id

Figure: Every listen has a user. A user can have no listens.

Listens.user_id is a non-NULL foreign key to Users.user_id, so every listen matches a user. The reverse is optional: Pluto is a user with no listens.

Figure: A subquery nests queries inside queries, read inside-out

Three concentric boxes. The innermost finds Taylor Swift songs (1, 2, 9); the middle finds the users who played them (1, 2, 3); the outer looks up their names (Mickey, Minnie, Daffy). Beside them, the three flavors of subquery, IN/NOT IN, EXISTS and Scalar, shown as equal peers.

Figure: Walking an IN subquery inside-out across three steps

Step 1 filters Songs to Taylor Swift, leaving song_ids 1, 2, 9. Step 2 finds the listens on those songs and takes the distinct users 1, 2, 3. Step 3 looks those users up in Users, giving Mickey, Minnie, Daffy.

Figure: The NULL-in-NOT-IN Trap

For rating 4.5, comparisons with 2.9 and 4.9 are TRUE; comparison with NULL is UNKNOWN. AND combines these to UNKNOWN, so WHERE drops this row. With NULL in the list, no row passes NOT IN: the query returns zero rows. Filtering NULL out of the list makes both comparisons for 4.5 TRUE, so this row passes. The filtered query returns six rows.

Figure: A scalar subquery returns one value that the outer query reuses for every row

The inner subquery runs once: AVG(rating) over all Listens, skipping the one NULL, gives 4.2 (blue, the single value). The outer query reuses 4.2 to keep every listen rated above it: Mickey 4.5, Minnie 4.7 and 4.6, Daffy 4.9 are kept. Of the 9 joined rows, Mickey 4.2 is not strictly greater, the two 3.9s and Daffy's 2.9 are below, and Daffy's NULL is UNKNOWN; those 5 are struck out, leaving 4 rows. Color key: blue = the one scalar value; the per-user bars are identity; grey strike = a dropped row.

Figure: Basic gives one global average; correlated gives each user their own

A Listens table grouped by user (Mickey blue, Minnie orange, Daffy purple) feeds two aggregations. A basic subquery sends all rows to one global average, 4.2, used for everyone. A correlated subquery sends each user's rows, along a colored arrow, to that user's own average: Mickey 4.2, Minnie 4.4, Daffy 3.9. The colored arrow is the correlation, WHERE user_id = u.user_id. Color key: blue Mickey, orange Minnie, purple Daffy; grey is the global path.

Figure: Intuition: trace the correlation with a double loop

Conceptual pseudocode, not a required execution plan: the outer loop runs once per user u; the inner SELECT AVG runs each pass over Listens l2 WHERE l2.user_id = u.user_id. An arrow links the inner reference u.user_id back to the outer loop variable u: that cross-reference is the correlation. Two scopes: u is the outer scope (current user), l2 is the inner scope (this query's Listens). The inner uses each user's ratings in this model: Mickey 4.2, Minnie 4.4, Daffy 3.9. The optimizer may choose an equivalent plan.

Figure: User ratings above their average

User ratings above their average. One row per rating, grouped by user. The conceptual trace uses each user's ratings (the correlation). Rows that fail the comparison are struck out; the survivors give the output.

Figure: NOT EXISTS: users with no rating above 4.5

NOT EXISTS: users with no rating above 4.5. One row per rating, grouped by user. The conceptual trace uses each user's ratings (the correlation). Rows that fail the comparison are struck out; the survivors exclude their users from the NOT EXISTS output.

Figure: CTEs: name the steps of a query

A 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: GROUP BY collapses; a window keeps the rows in a new table plus a column

The nine Listens rows. GROUP BY collapses them to three rows of per-user average. A window function instead returns the same nine Listens rows in a new table, each with a user_avg column holding its user average.

Figure: RANK in three steps: partition, order, assign

The RANK query, then three side-by-side tables: PARTITION BY makes three lanes, ORDER BY rating DESC NULLS LAST sorts each lane with NULL last, and the rank column numbers each lane from one.

Figure: ROW_NUMBER, RANK and DENSE_RANK on a tie

Four rows sorted by rating with two tied 4.5s. ROW_NUMBER is 1,2,3,4. RANK is 1,1,3,4 (skips after the tie). DENSE_RANK is 1,1,2,3 (no gap).

Figure: Hand-trace of the GROUP BY / HAVING query

The query, then three tables side by side: FROM+JOIN gives 9 rows, GROUP BY makes 6 user-genre groups, and HAVING COUNT >= 2 keeps 3 user-genre pairs.

Figure: The Funnel pattern: keep listeners, subtract sharers

A funnel narrowing from played 1000 to playlist 450 to shared 87, paired with a SELECT ... WHERE user_id IN (listens) AND user_id NOT IN (shares) query.

SELECT user_id, name
FROM users
-- Keep users who have listened.
WHERE user_id IN
  (SELECT user_id FROM listens)
-- Exclude users who have shared.
-- Assumes shares.user_id contains no NULLs.
  AND user_id NOT IN
  (SELECT user_id FROM shares)

Figure: The Comparison pattern: plays minus the genre average

Bars for three Pop artists against a dashed genre-average line, two above and one below, paired with plays - AVG(plays) OVER (PARTITION BY genre). Dua Lipa is 700 plays above the genre average, Doja Cat 100 above, and Sia 800 below.

SELECT artist_name, genre, plays,
  -- Subtract this genre's average.
  plays - AVG(plays) OVER (
    PARTITION BY genre
  ) AS vs_avg
  -- Positive: above average; negative: below.
FROM artist_stats

Figure: The Ladder pattern: top 3 songs per genre via ROW_NUMBER

A ladder of Pop songs numbered by plays; ranks 1 to 3 kept on the top rungs, rank 4 cut below the line, paired with ROW_NUMBER() OVER (PARTITION BY genre ORDER BY plays DESC) <= 3. Kept: 1 Levitating, 980 plays; 2 Blinding Lights, 900; 3 Stay, 870. Cut: 4 Peaches, 410.

SELECT * FROM (
  SELECT song, genre, plays,
    ROW_NUMBER() OVER (
      -- Number songs separately in each genre.
      PARTITION BY genre
      -- Most plays first.
      ORDER BY plays DESC
    ) AS rn
  FROM songs
) ranked
-- Keep up to three songs per genre.
WHERE rn <= 3

Figure: The Timeline pattern: running total with SUM OVER ORDER BY

A line climbing over four days as the running total accumulates 3, 8, 10, 14, paired with SUM(hours) OVER (ORDER BY date).

SELECT date, hours,
  SUM(hours) OVER (
    -- Add hours from earlier dates and today.
    ORDER BY date
  ) AS cumulative_hours
FROM daily_listening

Figure: Optional: flag the listen spike with LAG

A scatter of daily listens sitting near a normal band, with Thursday spiking to 310, a 138 percent jump, circled in red, paired with LAG(listens) and a percent-change > 50 filter.

WITH changes AS (
  SELECT song_id, date, listens,
    -- Previous count for this song.
    LAG(listens) OVER (
      PARTITION BY song_id ORDER BY date
    ) AS prev_listens
  FROM daily_listens
),
with_pct AS (
  SELECT *,
    -- Percentage change; NULL for a zero baseline.
    (listens - prev_listens) * 100.0
      / NULLIF(prev_listens, 0) AS pct_change
  FROM changes
)
SELECT * FROM with_pct
-- Keep increases over 50%.
WHERE pct_change > 50

Figure: Query equivalence vs output equivalence

Query equivalence: the same result for every valid input, like WHERE x BETWEEN 1 AND 5 versus x >= 1 AND x <= 5. Output equivalence: the same result on this data only, like WHERE rating > 4.7 versus rating >= 4.9, which agree until a row rates 4.8.

Figure: Query-equivalent: users who span at least 2 genres

Both count distinct genres per user, then keep counts of at least two. They are query-equivalent: the same result for every input allowed by the schema.

Figure: Query-equivalent: ratings above each user's personal average

Both return name-rating pairs where the rating exceeds that user's average. They are query-equivalent: the same result for every input allowed by the schema.

Figure: Top 3: output-equivalent today, not query-equivalent

Two queries for each user top 3 songs: ROW_NUMBER() less-equal 3, and a correlated subquery keeping songs with fewer than 3 rated higher. On distinct ratings both keep 3. Ignoring the extra rn column, compare selected listens. Add a tied 3.9: ROW_NUMBER keeps 3 rows (either tied listen can be omitted); the count query keeps 4. The tie at the cutoff is where they diverge.

Figure: Output equivalence and query equivalence

Output-equivalent: same result on this database. Query-equivalent: same result on every database allowed by the schema. One valid input with different results disproves query equivalence.

Figure: A Franken-table (or wide table) repeats itself

Left: a single wide table holding listen_id, name, email, title, artist, and rating. Mickey's email is copied on all three of his rows (highlighted amber), and the artist Taylor Swift is copied on row after row (highlighted amber). Right: three red cards naming the anomalies this repetition causes. Update: Mickey changes his email, so you must fix every Mickey row and will miss one. Insert: a brand-new song nobody has played has no listen row to live on. Delete: removing Mickey's last listen deletes his email too. Color key: amber marks a fact copied many times; red marks the three anomalies that repetition causes.

Figure: Give each fact one home

Three tables. Users holds user_id, name, and email, with a note that Mickey's email lives here once. Songs holds song_id, title, artist, and genre, with a note that Taylor Swift lives here once. Listens at the bottom holds listen_id, user_id, song_id, and rating, and two arrows run from its user_id to Users and from its song_id to Songs, the foreign keys. Color key: green is the normalized design; the arrows are foreign keys that point at the one place each fact lives.

Figure: How one query runs: five stages

The top-10 most-played songs query moves through five stages, left to right. 1, parse and analyze the SQL. 2, choose a plan of operators: scan, join, group, sort. 3, the coordinator splits the scan into tasks. 4, workers run the tasks in parallel and compute partial counts. 5, merge the partial counts, sort, and return the top 10.

Figure: A small vocabulary spans a large answerable domain

Three white panels. DEFINED (blue) holds parameterized primitives listener_of, active_since, liked, each pinning a choice the model would guess; a dashed power_user chip is the primitive you add next. Composing feeds the green ANSWERABLE panel of questions, none written out. Adding power_user to the DEFINED set unlocks a new dashed green composition, so the domain grows by adding to the blue. A grey OFF THE TABLE panel holds questions no definition reaches: subjective, missing-data, predictive.