M1 Spotify Database The course runs on three tables: Users, Songs, and Listens.
2 figures spotify-db · The Three Tables The Spotify schema: Users, Songs, and Listens, joined by two foreign keys 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. One schema: three tables, two keys tie them together Every query in the course is a question over these. The IDs are how they connect. Users (4 rows) user_id PK name email 1 Mickey mickey@ex.com 2 Minnie minnie@ex.com 3 Daffy daffy@ex.com 4 Pluto pluto@ex.com PK = primary key user_id is one unique value per user, like a student ID or a social security number. No two users share it. Songs (10 rows) song_id PK title artist genre 1 Evermore Taylor Swift Pop 2 Willow Taylor Swift Pop 3 Shape of You Ed Sheeran Rock 4 Photograph Ed Sheeran Rock 5 Shivers Ed Sheeran Rock 6 Yesterday Beatles Classic 7 Yellow Sub Beatles Classic 8 Hey Jude Beatles Classic 9 Bad Blood Taylor Swift Rock 10 DJ Mix DJ NULL Listens (9 rows) listen_id PK user_id FK song_id FK rating listen_time 1 1 1 4.5 2024-08-30 2 1 2 4.2 NULL 3 1 6 3.9 2024-08-29 4 2 2 4.7 NULL 5 2 7 4.6 2024-08-28 6 2 8 3.9 2024-08-27 7 3 1 2.9 NULL 8 3 2 4.9 2024-08-26 9 3 6 NULL NULL Reading one row listen 8: user_id 3, song_id 2, rating 4.9 Daffy played Willow, and rated it 4.9. user_id = Users.user_id song_id = Songs.song_id FK = foreign key user_id and song_id are foreign keys. Each must match a real id in Users or Songs, so a listen can't point at nobody. The whole schema on one page. Each table has a primary key (blue PK): user_id, song_id, listen_id. Listens carries two foreign keys (blue FK) that point back at the other tables, so one Listens row reads as a sentence: listen 8 is Daffy (user 3) playing Willow (song 2), rated 4.9. Every query in the course joins these three tables along those IDs.
Notes ↗ spotify-db · How the Schema Is Defined Reading SQL DDL: how the Listens table is defined 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. Reading the DDL: how the Listens table is defined DDL, the Data Definition Language: CREATE TABLE names each column, types it, and states its rules. CREATE TABLE listens ( listen_id INT PRIMARY KEY, user_id INT NOT NULL, song_id INT NOT NULL, rating DECIMAL(2,1), listen_time TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (song_id) REFERENCES songs(song_id) ); PRIMARY KEY listen_id is the unique id for the row. NOT NULL = required; rating / listen_time omit it, so those may be NULL. the column's type DECIMAL(2,1) holds a rating like 4.5. INT, TIMESTAMP - every column names one. FOREIGN KEY ... REFERENCES every user_id must be a real Users row, every song_id a real Songs row. The DB rejects a listen that points at nobody. Users and Songs are defined the same way, just without the foreign keys. CREATE TABLE assigns each column a name, a type (INT, DECIMAL(2,1), TIMESTAMP), and constraints. PRIMARY KEY selects the unique row identifier. NOT NULL requires a value. FOREIGN KEY ... REFERENCES constrains user_id and song_id to values that exist in the parent table.
Notes ↗ M1 SELECT-FROM-WHERE: The SQL Foundation A basic query has three clauses.
3 figures select-from-where SELECT-FROM-WHERE keeps some rows, then some columns 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. SELECT-FROM-WHERE: keep some rows, then some columns Execution order: FROM loads rows, WHERE filters rows, SELECT picks columns. FROM Listens 9 rows listen_id user_id song_id rating 1 1 1 4.5 2 1 2 4.2 3 1 6 3.9 4 2 2 4.7 5 2 7 4.6 6 2 8 3.9 7 3 1 2.9 8 3 2 4.9 9 3 6 NULL WHERE rating > 4.0 keep 5 rows, then 3 columns SELECT user_id, song_id, rating 5 rows user_id song_id rating 1 1 4.5 1 2 4.2 2 2 4.7 2 7 4.6 3 2 4.9 rows survive WHERE, then SELECT drops listen_id FROM produces nine Listens rows. WHERE keeps the five rows where rating > 4.0 evaluates to TRUE and drops rows where it evaluates to FALSE or UNKNOWN, including the NULL rating. SELECT returns only user_id, song_id, and rating and drops listen_id.
Notes ↗ select-from-where · WHERE with AND and OR AND keeps a row only when both conditions hold; OR when either does 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. WHERE with AND and OR: same two conditions, opposite strictness Keep a row only when BOTH hold SELECT user_id, song_id, rating FROM Listens WHERE user_id = 1 AND rating > 4.0 user_id rating 1 4.5 ✓ keep 1 4.2 ✓ keep 1 3.9 fails WHERE 2 4.7 fails WHERE 2 4.6 fails WHERE 2 3.9 fails WHERE 3 2.9 fails WHERE 3 4.9 fails WHERE 3 NULL fails WHERE AND keeps 2 rows Keep a row when EITHER holds SELECT user_id, song_id, rating FROM Listens WHERE user_id = 1 OR rating > 4.0 user_id rating 1 4.5 ✓ keep 1 4.2 ✓ keep 1 3.9 ✓ keep 2 4.7 ✓ keep 2 4.6 ✓ keep 2 3.9 fails WHERE 3 2.9 fails WHERE 3 4.9 ✓ keep 3 NULL fails WHERE OR keeps 6 rows AND keeps a row when both predicates evaluate to TRUE, so two rows survive. OR keeps a row when either predicate evaluates to TRUE, so six rows survive. The NULL rating makes rating > 4.0 evaluate to UNKNOWN, and UNKNOWN does not satisfy WHERE.
Notes ↗ select-from-where · Understanding Aliases (AS Keyword) An alias renames the output column, but WHERE cannot use it 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. Aliases (AS): rename a column, but WHERE can't use the new name AS renames the output column rating rating * 2 ( = double_rating ) 4.5 9.0 4.2 8.4 3.9 7.8 Fails SELECT rating * 2 AS double_rating FROM Listens WHERE double_rating > 8 ✗ ERROR: no column named double_rating yet Works SELECT rating * 2 AS double_rating FROM Listens WHERE rating * 2 > 8 ✓ WHERE uses the expression, which already exists Execution order: the alias is created last 1 FROM Listens 2 WHERE … 3 SELECT … AS double_rating double_rating is born HERE (3rd) WHERE runs 2nd, before the alias exists AS names an expression in the result produced by SELECT. WHERE runs earlier, so WHERE double_rating > 8 fails. WHERE can evaluate WHERE rating * 2 > 8 because it uses the underlying expression.
Notes ↗ M1 NULL Values: The Unknown in SQL NULL means "unknown," not 0 or empty.
6 figures nulls NULL adds a third truth value, UNKNOWN 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, so UNKNOWN and NULL drop silently; rating IS NULL is the only test that returns a real TRUE or FALSE and finds NULL rows. Color key: green TRUE, red FALSE, grey question mark UNKNOWN. NULL adds a third truth value: UNKNOWN NULL is not zero and not empty. It means unknown, and it spreads. expression result Binary logic: every test is TRUE or FALSE 4.5 > 4.0 ✓ TRUE 4.5 > 5.0 ✗ FALSE Add NULL, a missing value: every comparison with it is UNKNOWN, a third truth value. 4.5 > NULL ? UNKNOWN NULL = NULL ? UNKNOWN rating = NULL ? UNKNOWN WHERE keeps only TRUE rows, so UNKNOWN (and NULL) drop silently. rating IS NULL is the only test that gives a real TRUE/FALSE and finds them. SQL uses three-valued logic. Comparisons with NULL evaluate to UNKNOWN. WHERE keeps TRUE and drops FALSE and UNKNOWN.
Notes ↗ nulls · Two-Valued Logic Refresher Two-valued boolean logic: AND, OR, NOT truth tables 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. Two-valued logic: every test is TRUE or FALSE The refresher before NULL: AND needs both, OR needs one, NOT flips. a AND b a b AND T T T T F F F T F F F F a OR b a b OR T T T T F T F T T F F F NOT a a NOT T F F T Two-valued boolean logic. AND requires TRUE on both sides. OR requires TRUE on at least one side. NOT flips the value.
Notes ↗ nulls · What Changes When NULL Enters NULL's rules in two groups: operators vs comparisons NULL's rules in two groups: operators vs comparisons NULL's rules in two groups: operators vs comparisons Color key: green TRUE, red FALSE, grey NULL/UNKNOWN. NULL's rules, in two groups Operators have a dominant value; comparisons all go UNKNOWN; only IS NULL escapes. Operators: a value dominates expression result FALSE AND NULL FALSE false wins TRUE AND NULL NULL TRUE OR NULL TRUE true wins FALSE OR NULL NULL NOT NULL NULL NULL stays Comparisons go UNKNOWN; IS NULL is the escape expression result 4.5 > NULL NULL NULL wins 4 = NULL NULL NULL = NULL NULL rating IS NULL TRUE / FALSE the real test Boolean operators sometimes return a definite value with NULL. Comparisons with NULL return UNKNOWN. IS NULL returns TRUE or FALSE.
Notes ↗ nulls · NULL with AND/OR Logic One NULL row: TRUE AND UNKNOWN drops it, TRUE OR UNKNOWN keeps it One NULL row: TRUE AND UNKNOWN drops it, TRUE OR UNKNOWN keeps it One NULL row: TRUE AND UNKNOWN drops it, TRUE OR UNKNOWN keeps it Color key: green TRUE, red FALSE, grey NULL/UNKNOWN. Same row, two operators, two fates Daffy's row, run through AND on the left and OR on the right. The operator decides whether the NULL survives. AND SELECT name FROM Listens WHERE user_id = 3 AND rating > 4 OR SELECT name FROM Listens WHERE user_id = 3 OR rating > 4 user_id = 3 ? TRUE rating > 4 ? UNKNOWN user_id = 3 ? TRUE rating > 4 ? UNKNOWN TRUE AND UNKNOWN = UNKNOWN Daffy user_id = 3 rating = NULL row dropped TRUE OR UNKNOWN = TRUE Daffy user_id = 3 rating = NULL row kept
One row, two operators. Daffy's user_id = 3 is TRUE but rating > 4 is UNKNOWN. Under AND the UNKNOWN drops the row; under OR the TRUE keeps it. The operator decides whether a NULL survives.
Notes ↗ nulls · NULL in WHERE Clause The same NULL row across three WHERE filters 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. The NULL row: kept only by IS NULL, dropped by every comparison Query 1 SELECT listen_id, rating FROM Listens WHERE rating IS NULL listen_id rating 9 NULL 1 row: IS NULL keeps it Query 2 SELECT listen_id, rating FROM Listens WHERE rating > 4.0 listen_id rating 1 4.5 2 4.2 4 4.7 5 4.6 8 4.9 5 rows: NULL dropped Query 3 SELECT listen_id, rating FROM Listens WHERE rating <= 4.0 listen_id rating 3 3.9 6 3.9 7 2.9 3 rows: NULL dropped again Row 9 is NULL. Only IS NULL (rule 5) returns TRUE; > and <= return UNKNOWN, which WHERE drops. One NULL row, three filters. IS NULL (rule 5) returns TRUE and keeps row 9; both rating > 4.0 and rating <= 4.0 evaluate to UNKNOWN on a NULL, and WHERE keeps only TRUE rows, so the NULL is gone from both.
Notes ↗ nulls · NULL in Aggregates 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 COUNT(*)=9 counts all; COUNT(rating)=8 and AVG=4.2 skip the NULL Color key: green TRUE, red FALSE, grey NULL/UNKNOWN. NULL and aggregates: counted once, skipped twice COUNT(*) counts every row; COUNT(rating) and AVG skip the NULL. Listens.rating rating 4.5 4.2 3.9 4.7 4.6 3.9 2.9 4.9 NULL COUNT(*) counts every row, including the NULL 9 COUNT(rating) skips the NULL: 8 ratings, not 9 rows 8 AVG(rating) 33.6 ÷ 8 = 4.2 (8 ratings, not 9 rows) 4.2 COUNT(*) counts every row, so it returns 9. COUNT(rating) counts non-NULL values, so it returns 8. AVG(rating) divides by that same non-NULL count of 8, not 9.
Notes ↗ M1 GROUP BY: Grouping and Aggregating Data GROUP BY partitions the input rows by the named column values and produces one output row per partition.
5 figures groupby GROUP BY collapses a nine-row Listens table into a three-row grouped table 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. Listens 9 rows user_id song_id rating 1 1 4.5 1 2 4.2 1 6 3.9 2 2 4.7 2 7 4.6 2 8 3.9 3 1 2.9 3 2 4.9 3 6 NULL GROUP BY user_id Grouped by user_id 3 rows user_id COUNT(*) AVG 1 3 4.2 2 3 4.4 3 3 3.9 GROUP BY produces one output row per group: nine listen rows become three rows keyed by user_id, each row carries aggregate values. Nine rows, three groups, three output rows.
Notes ↗ groupby · Simple GROUP BY GROUP BY user_id: nine listens collapse into three buckets, one row each GROUP BY user_id: nine listens collapse into three buckets, one row each GROUP BY user_id collapses nine Listens rows into three buckets, one per user, each with COUNT of three. Pluto has no listens, so Pluto gets no bucket and no output row. Color key: per-user identity bars (Mickey blue, Minnie orange, Daffy purple, Pluto cyan). GROUP BY user_id: same user_id falls in one bucket Nine listens collapse into three buckets, one row each. SELECT user_id, COUNT(*) AS listen_count FROM Listens GROUP BY user_id Listens (9 rows, bucketed by user) the input listen_id user_id song_id rating 1 1 1 4.5 2 1 2 4.2 3 1 6 3.9 4 2 2 4.7 5 2 7 4.6 6 2 8 3.9 7 3 1 2.9 8 3 2 4.9 9 3 6 NULL GROUP BY Grouped by user_id (3 rows) one row per bucket user_id listen_count 1 3 2 3 3 3 Pluto: 0 listens, so no bucket and no row. The nine input listens (left, bucketed by user) collapse into the three-row output (right): one row per bucket, COUNT(*) = 3 each. Pluto has zero listens, so Pluto forms no bucket and appears in no output row.
Notes ↗ groupby · GROUP BY with Multiple Aggregates GROUP BY user_id with four aggregates per group: COUNT, AVG, MAX, MIN 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. Listens 9 rows user_id song_id rating 1 1 4.5 1 2 4.2 1 6 3.9 2 2 4.7 2 7 4.6 2 8 3.9 3 1 2.9 3 2 4.9 3 6 NULL GROUP BY user_id Grouped by user_id 3 rows 1 2 3 COUNT 3 3 3 AVG 4.2 4.4 3.9 MAX 4.5 4.7 4.9 MIN 3.9 3.9 2.9 Notes ↗ groupby · GROUP BY Multiple Columns Multi-column GROUP BY splits one bucket into finer buckets Multi-column GROUP BY splits one bucket into finer buckets By user_id, Mickey is one bucket of three rows with COUNT three. Adding song_id splits it into three buckets of one row each, COUNT one. More columns, finer buckets Mickey's three listens: one bucket by user_id; add song_id and the bucket splits into three. GROUP BY user_id Mickey user_id = 1 song_id rating 1 4.5 2 4.2 6 3.9 1 bucket COUNT(*) = 3 + song_id GROUP BY user_id, song_id (1, 1) rating 4.5 COUNT(*) = 1 (1, 2) rating 4.2 COUNT(*) = 1 (1, 6) rating 3.9 COUNT(*) = 1 3 buckets The group key consists of the listed columns. GROUP BY user_id forms one group for Mickey with three rows. GROUP BY user_id, song_id forms three groups for Mickey, one per (user, song) pair, and each group contains one row.
Notes ↗ groupby · Common Mistakes A grouped row cannot show a column you did not group by 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. Ask for a column you did not group by, and the row cannot answer GROUP BY user_id makes one row per user. But song_id had three values in that group. SELECT user_id, song_id, COUNT(*) FROM Listens GROUP BY user_id After GROUP BY user_id: one row per user user_id COUNT(*) 1 3 2 3 3 3 song_id ? collapsed songs 1, 2, 6 - which one? ? collapsed songs 2, 7, 8 - which one? ? collapsed songs 1, 2, 6 - which one? song_id was never grouped, so it has no single value Every column you SELECT must be grouped or aggregated. Notes ↗ M1 HAVING: Filtering Group Aggregates HAVING filters groups after GROUP BY by evaluating aggregate expressions such as COUNT and AVG.
4 figures having An average rating filter on groups: HAVING tests each group's AVG(rati An average rating filter on groups: HAVING tests each group's AVG(rati 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. HAVING filters GROUPS (after GROUP BY) SELECT user_id, COUNT (*), AVG (rating) FROM Listens GROUP BY user_id HAVING AVG (rating) > 4.0 GROUP BY user_id -> one row per user 3 groups user_id COUNT(*) AVG(rating) 1 3 4.2 2 3 4.4 3 3 3.9 4.2 > 4.0 keep 4.4 > 4.0 keep 3.9 > 4.0 fails Output: Mickey, Minnie (Daffy's 3.9 is below 4.0) HAVING is the gatekeeper on groups. Groups form with their aggregates, HAVING tests each one, and a group either passes whole or drops whole. Three groups enter, two survive.
Notes ↗ having · HAVING with COUNT HAVING COUNT(*) >= 2 on listens grouped by song_id: songs 1, 2 and 6 w HAVING COUNT(*) >= 2 on listens grouped by song_id: songs 1, 2 and 6 w HAVING COUNT(*) >= 2 on listens grouped by song_id: songs 1, 2 and 6 were played at least twice and stay; songs 7 and 8 were played once and drop. Color key: Mickey blue, Minnie orange, Daffy purple; struck rows/groups are filtered out. HAVING with COUNT: keep only songs played twice or more SELECT song_id, COUNT (*), AVG (rating) FROM Listens GROUP BY song_id HAVING COUNT (*) >= 2 GROUP BY song_id -> one row per song 5 groups song_id COUNT(*) AVG(rating) 1 2 3.7 2 3 4.6 6 2 3.9 7 1 4.6 8 1 3.9 2 >= 2 keep 3 >= 2 keep 2 >= 2 keep 1 >= 2 fails 1 >= 2 fails Output: songs 1, 2, 6 (played at least twice) Grouping listens by song_id and keeping only groups with COUNT(*) >= 2. Songs 1, 2 and 6 were played at least twice; songs 7 and 8 were played once, so they drop.
Notes ↗ having · HAVING with Multiple Conditions HAVING COUNT(*) >= 3 AND AVG(rating) > 4.0: both conditions must hold. HAVING COUNT(*) >= 3 AND AVG(rating) > 4.0: both conditions must hold. HAVING COUNT(*) >= 3 AND AVG(rating) > 4.0: both conditions must hold. Mickey and Minnie pass both; Daffy has three listens but his 3.9 average fails the rating test, so his whole group drops. Color key: Mickey blue, Minnie orange, Daffy purple; struck rows/groups are filtered out. HAVING with AND: a group must pass BOTH conditions SELECT user_id, COUNT (*), AVG (rating), MIN(rating) FROM Listens GROUP BY user_id HAVING COUNT (*) >= 3 AND AVG (rating) > 4.0 GROUP BY user_id -> one row per user 3 groups user_id COUNT(*) AVG(rating) MIN(rating) 1 3 4.2 3.9 2 3 4.4 3.9 3 3 3.9 2.9 both pass keep both pass keep AVG 3.9 not > 4.0 fails Output: Mickey, Minnie (Daffy's 3.9 average fails the second test) Two HAVING conditions joined by AND: a group survives only if it passes both. Mickey and Minnie clear COUNT(*) >= 3 and AVG(rating) > 4.0; Daffy has three listens but his 3.9 average fails the second test.
Notes ↗ having · WHERE and HAVING WHERE rating > 3.5 strikes Daffy's 2.9 and NULL first; the survivors g WHERE rating > 3.5 strikes Daffy's 2.9 and NULL first; the survivors g WHERE rating > 3.5 strikes Daffy's 2.9 and NULL first; the survivors group into Mickey 3, Minnie 3, Daffy 1; HAVING COUNT(*) >= 2 then drops Daffy's one-row group. Color key: Mickey blue, Minnie orange, Daffy purple; struck rows/groups are filtered out. WHERE filters rows, then HAVING filters groups SELECT user_id, COUNT (*), AVG (rating) FROM Listens WHERE rating > 3.5 GROUP BY user_id HAVING COUNT (*) >= 2 WHERE rating > 3.5 9 -> 7 rows user_id rating 1 4.5 1 4.2 1 3.9 2 4.7 2 4.6 2 3.9 3 2.9 3 4.9 3 NULL GROUP GROUP BY -> HAVING COUNT(*) >= 2 3 -> 2 user_id COUNT(*) 1 3 2 3 3 1 3 >= 2 keep 3 >= 2 keep 1 >= 2 fails Output: Mickey, Minnie WHERE and HAVING run at different times. WHERE strikes individual rows before grouping (Daffy's 2.9 and NULL go), the survivors form groups, then HAVING drops whole groups by aggregate (Daffy's single row fails COUNT(*) >= 2). WHERE picks rows, HAVING picks groups.
Notes ↗ M1 SQL Sets and Set Operations Set operators combine two query results.
1 figure sets · SQL Set Operations Overview The four set operations as row tables 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. Four set operations on two query results Combine A and B four ways. Color = membership: teal both, blue only A, violet only B. Query A users who like Pop name Mickey Minnie Query B users who like Rock name Mickey Daffy combine the two results four ways: UNION either side, deduped name Mickey Minnie Daffy UNION ALL either side, keep dups name Mickey Minnie Mickey Daffy Mickey appears twice INTERSECT in BOTH A and B name Mickey EXCEPT in A, not in B name Minnie Two example user sets, with the result of each set operator.
Notes ↗ M1 JOINs: Combining Tables A JOIN pairs rows from two tables when a predicate in ON evaluates to true.
5 figures joins Users joined to Listens on user_id 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. SELECT u.name, l.song_id, l.rating FROM Users u JOIN Listens l ON u.user_id = l.user_id ORDER BY u.user_id Users 4 rows user_id name 1 Mickey 2 Minnie 3 Daffy 4 Pluto Listens 9 rows user_id song_id rating 1 1 4.5 1 2 4.2 1 6 3.9 2 2 4.7 2 7 4.6 2 8 3.9 3 1 2.9 3 2 4.9 3 6 NULL on no match Users ⋈ Listens 9 rows user_id name song_id rating 1 Mickey 1 4.5 1 Mickey 2 4.2 1 Mickey 6 3.9 2 Minnie 2 4.7 2 Minnie 7 4.6 2 Minnie 8 3.9 3 Daffy 1 2.9 3 Daffy 2 4.9 3 Daffy 6 NULL JOIN ON user_id INNER JOIN → matched pairs only · 9 rows Users stores user names; Listens stores plays. A join matches each Users row with Listens rows that share the same user_id. Mickey has 3 rows in Listens, so the join outputs 3 rows for Mickey, one per play. INNER JOIN outputs only matched pairs, so Pluto contributes no rows: 9 rows.
Notes ↗ joins · Outer Joins: LEFT and RIGHT Users joined to Listens on user_id; LEFT JOIN keeps the unmatched Pluto 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. SELECT 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 Users 4 rows user_id name 1 Mickey 2 Minnie 3 Daffy 4 Pluto Listens 9 rows user_id song_id rating 1 1 4.5 1 2 4.2 1 6 3.9 2 2 4.7 2 7 4.6 2 8 3.9 3 1 2.9 3 2 4.9 3 6 NULL on no match Users ⋈ Listens INNER 9 · LEFT 10 user_id name song_id rating 1 Mickey 1 4.5 1 Mickey 2 4.2 1 Mickey 6 3.9 2 Minnie 2 4.7 2 Minnie 7 4.6 2 Minnie 8 3.9 3 Daffy 1 2.9 3 Daffy 2 4.9 3 Daffy 6 NULL JOIN ON user_id INNER JOIN → matched pairs only · 9 rows 4 Pluto NULL NULL LEFT JOIN → keeps Pluto, song_id / rating NULL-filled · 10 rows Notes ↗ joins · Outer Joins: LEFT and RIGHT Songs joined to Listens on song_id; RIGHT JOIN keeps the five unplayed songs Songs joined to Listens on song_id; RIGHT JOIN keeps the five unplayed songs A Songs table and a Listens table 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. SELECT s.title, l.rating FROM Songs s RIGHT JOIN Listens l ON s.song_id = l.song_id Songs 10 rows song_id title 1 Evermore 2 Willow 3 Shape of You 4 Photograph 5 Shivers 6 Yesterday 7 Yellow Submarine 8 Hey Jude 9 Bad Blood 10 DJ Mix Listens 9 rows song_id rating 1 4.5 1 2.9 2 4.2 2 4.7 2 4.9 6 3.9 6 NULL 7 4.6 8 3.9 on no match no match no match no match no match Songs ⋈ Listens INNER 9 · RIGHT 14 song_id title rating 1 Evermore 4.5 1 Evermore 2.9 2 Willow 4.2 2 Willow 4.7 2 Willow 4.9 6 Yesterday 3.9 6 Yesterday NULL 7 Yellow Submarine 4.6 8 Hey Jude 3.9 RIGHT JOIN ON song_id INNER JOIN → only songs with a listen · 9 rows 3 Shape of You NULL 4 Photograph NULL 5 Shivers NULL 9 Bad Blood NULL 10 DJ Mix NULL RIGHT JOIN → keeps the 5 songs nobody played, NULL-filled · 14 rows Five songs have no matching listens. INNER JOIN excludes those songs. RIGHT JOIN keeps every song and fills the listen columns with NULL for the five songs with no plays. The preserved unmatched side changes when the query swaps LEFT and RIGHT.
Notes ↗ joins · Foreign Keys A foreign key turns one outer join into an inner join A foreign key turns one outer join into an inner join Listens.user_id is a foreign key referencing Users, so every listen has a real user; only Pluto, a user with no listen, is unmatched. Two statements follow: for Users JOIN Listens, RIGHT JOIN equals INNER JOIN (only LEFT JOIN adds Pluto); for Listens JOIN Users, LEFT JOIN equals INNER JOIN (only RIGHT JOIN adds Pluto). The rule: keeping the Listens side equals the inner join, keeping the Users side surfaces the orphan Pluto. Songs and song_id behave the same way. A foreign key turns one outer join into a plain INNER join Reason it through from one fact about the key. Listens Users user_id Listens.user_id is a foreign key: every listen has a real user. The only unmatched row is Pluto, a user with no listen. Users JOIN Listens RIGHT JOIN = INNER JOIN every listen has a user, so keeping all the listens adds nothing new. only LEFT JOIN adds Pluto Listens JOIN Users LEFT JOIN = INNER JOIN every listen has a user, so keeping all the listens adds nothing new. only RIGHT JOIN adds Pluto Keep the Listens side → the outer join is just INNER. Keep the Users side → Pluto, the orphan, appears. (Songs and song_id work exactly the same way.) Notes ↗ joins · Common Mistakes Forgetting the ON clause makes a cartesian product Forgetting the ON clause makes a cartesian product A 4 by 9 grid pairs every one of four users with every one of nine listens, giving 36 rows. Only nine cells, where the user matches the listen owner, are real matches; the other 27 are meaningless. Adding ON user_id keeps only the nine. Forget the ON clause: every row paired with every row Users Listens are coloured by who played them Mickey Minnie Daffy Pluto L1 L2 L3 L4 L5 L6 L7 L8 L9 ✓ ✓ ✓ ✕ ✕ ✕ ✕ ✕ ✕ ✕ ✕ ✕ ✓ ✓ ✓ ✕ ✕ ✕ ✕ ✕ ✕ ✕ ✕ ✕ ✓ ✓ ✓ ✕ ✕ ✕ ✕ ✕ ✕ ✕ ✕ ✕ 4 users × 9 listens = 36 rows. Only the 9 green cells match on user_id; the other 27 are meaningless. The fix is one clause: ON u.user_id = l.user_id.
Notes ↗ M1 Subqueries: Basic Patterns A subquery is a SELECT nested inside another query.
4 figures subqueries-basic A subquery nests queries inside queries, read inside-out 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. Subqueries: queries inside queries Read inside-out: the innermost query runs first and feeds the next, layer by layer. 3 Look up their names Mickey, Minnie, Daffy 2 Find users who played them {1, 2, 3} 1 Find Taylor Swift songs {1, 2, 9} Three flavors IN / NOT IN is a value in the inner list? EXISTS does the inner query return any row? Scalar compare against one inner value
A subquery is a query inside a query, read inside-out: the innermost finds Taylor Swift songs, the middle finds who played them, the outer returns their names. The same nesting drives IN / NOT IN, EXISTS, and Scalar.
Notes ↗ subqueries-basic · IN Subquery: Find Taylor Swift Listeners Walking an IN subquery inside-out across three steps 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. IN subquery: walk the data inside-out 1. Songs WHERE artist = 'Taylor Swift' 10 → 3 song_id artist 1 Taylor Swift 2 Taylor Swift 3 Ed Sheeran 4 Ed Sheeran 5 Ed Sheeran 6 Beatles 7 Beatles 8 Beatles 9 Taylor Swift 10 DJ {1, 2, 9} feeds 2. Listens on those songs distinct user_id song_id 1 1 1 2 2 2 3 1 3 2 {1, 2, 3} feeds 3. Users for {1, 2, 3} 3 rows name user_id Mickey 1 Minnie 2 Daffy 3 Result: Mickey, Minnie, Daffy Notes ↗ subqueries-basic · Scalar Subquery: Above Average Ratings A scalar subquery returns one value that the outer query reuses for every row 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; Mickey 4.2 is not strictly greater and Daffy's NULL is UNKNOWN, both struck out, leaving 4 rows. Color key: blue = the one scalar value; the per-user bars are identity; grey strike = a dropped row. Scalar subquery: one value, reused for every row SELECT u.name, l.rating FROM Users u JOIN Listens l ON u.user_id = l.user_id WHERE l.rating > ( SELECT AVG (rating) FROM Listens ) Inner subquery, runs once AVG(rating) over all Listens the one NULL rating is skipped (8 ratings) = 4.2 one value, every row Outer query: keep l.rating > 4.2 name rating > 4.2 ? Mickey 4.5 ✓ Mickey 4.2 ✗ Minnie 4.7 ✓ Minnie 4.6 ✓ Daffy 4.9 ✓ Daffy NULL ? UNKNOWN 4 rows kept A scalar subquery returns one value. The inner query computes AVG(rating) over Listens and skips the NULL, producing 4.2. The outer query compares each row's rating to 4.2 and keeps four rows. Mickey's 4.2 fails a strict > comparison, and Daffy's NULL > 4.2 evaluates to UNKNOWN, so both rows drop.
Notes ↗ subqueries-basic · Common Mistakes A NULL inside NOT IN silently returns zero rows; the IS NOT NULL guard fixes it A NULL inside NOT IN silently returns zero rows; the IS NOT NULL guard fixes it Two columns. Left (red, the failure): the unsafe query rating NOT IN (Daffy's ratings) expands to 4.5 != 2.9 AND 4.5 != 4.9 AND 4.5 != NULL; the first two are TRUE but the comparison against NULL is UNKNOWN (grey, marked ?), so the whole AND is UNKNOWN and WHERE drops every row: 0 rows. Right (green, the fix): adding AND rating IS NOT NULL removes the NULL from the list, both comparisons are TRUE, and 6 rows come back. Color key: red = the silent failure, green = the guarded query, grey with ? = the UNKNOWN NULL comparison. NOT IN with a NULL: guard it, or lose every row Run this query what does it return? SELECT listen_id, rating FROM Listens WHERE rating NOT IN ( SELECT rating FROM Listens WHERE user_id = 3 ) Daffy's ratings: 2.9, 4.9, and a NULL 4.5 != 2.9 ✓ TRUE 4.5 != 4.9 ✓ TRUE 4.5 != NULL ? UNKNOWN TRUE AND TRUE AND UNKNOWN = UNKNOWN WHERE drops every row 0 rows ✗ The fix add AND rating IS NOT NULL SELECT listen_id, rating FROM Listens WHERE rating NOT IN ( SELECT rating FROM Listens WHERE user_id = 3 AND rating IS NOT NULL ) The NULL never enters the list: (2.9, 4.9) 4.5 != 2.9 ✓ TRUE 4.5 != 4.9 ✓ TRUE TRUE AND TRUE = TRUE every matching row is kept 6 rows ✓
Notes ↗ M1 CTEs: Common Table Expressions A CTE (WITH name AS (...)) names an intermediate query result so the rest of the query can reference it like a table.
3 figures cte CTEs: turn nested chaos into named, linear steps
CTEs: turn nested chaos into named, linear steps
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 {3,4,5,9}, 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.
CTEs: turn nested chaos into named, linear steps
Goal: find the users who listen to Classic songs.
Nested subqueries
Read inside-out · hard to debug
SELECT name FROM Users
WHERE user_id IN ( ... )
Step 3 (outer)
SELECT user_id FROM Listens
WHERE song_id IN ( ... )
Step 2 (middle)
SELECT song_id FROM Songs
WHERE genre = 'Classic'
Step 1 (innermost)
Problems
• Must read inside-out
• Hard to debug a single step
• Cannot reuse a piece
?
?
?
CTE approach
Linear · clear · maintainable
WITH ClassicSongs
Step 1: find Classic songs
SELECT song_id FROM Songs WHERE genre = 'Classic'
→ {6, 7, 8}
ClassicListeners
Step 2: find users who listened
SELECT DISTINCT user_id FROM Listens
WHERE song_id IN (SELECT song_id FROM ClassicSongs)
→ {1, 2, 3}
Final query
Step 3: get user names
SELECT name FROM Users
WHERE user_id IN (SELECT user_id FROM ClassicListeners)
→ {Mickey, Minnie, Daffy}
Benefits
✓ Each step has a name ✓ Self-documenting
✓ Easy to debug ✓ Reuse a CTE
Test each intermediate result on its own
transform
Color key red = the hard-to-read nested way · green = the clear CTE way · grey = a neutral step label
A CTE turns nested chaos into named, linear steps. Left (red): subqueries nested inside one another, read inside-out and hard to debug. Right (green): the same logic as named steps read top to bottom. Same result, far easier to follow.
Notes ↗ cte · Why CTEs? Three reasons CTEs are first-class Three reasons CTEs are first-class Readable named blocks, optimizer can reason about each block on its own, and recursion lets a CTE reference itself to walk hierarchies. Why databases made CTEs first-class Not just sugar for subqueries. Three real wins. 1 Readable Each step is a named block, read top to bottom, not inside-out. 2 Optimizer-friendly The engine can reason about and optimize each named block on its own. 3 Recursion A CTE can reference itself: walk org charts, trees, and dependency graphs.
Notes ↗ cte · Common Patterns CTE common patterns and performance notes CTE common patterns and performance notes A CTE names a result you can reference several times instead of repeating a subquery; index the base-table columns its result joins on; and whether the engine computes the CTE once (MATERIALIZED, a reused temp result) or inlines it into the query and may re-run it depends on the database. Common patterns and performance notes A CTE names a result. Whether the engine computes it once or re-runs it depends on the engine. A reusable named step WITH gives a result a name. Reference it several times in one query, instead of repeating the same subquery. Index the join keys A CTE result joins back on source columns, so index those base-table columns to keep the join fast. Materialized or inlined Computed once, or re-run? It depends. MATERIALIZED keeps it as a reused temp result. Without that, engines may inline the CTE and push filters in.
Notes ↗ M1 Window Functions: Calculations Without Collapsing Window functions compute aggregates across a partition defined by OVER, returning one result per input row instead of collapsing them like GROUP BY.
6 figures window-functions · GROUP BY vs PARTITION BY GROUP BY collapses; a window keeps the rows in a new table plus a column 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. GROUP BY collapses the rows; a window keeps them and adds a column Same per-user average, two outputs. The window result is the Listens rows again, plus one column. Listens user_id song_id rating 1 1 4.5 1 2 4.2 1 6 3.9 2 2 4.7 2 7 4.6 2 8 3.9 3 1 2.9 3 2 4.9 3 6 NULL GROUP BY user_id user_id avg_rating 1 4.2 2 4.4 3 3.9 GROUP BY 9 rows → 3, detail gone AVG(rating) OVER (PARTITION BY user_id) user_id song_id rating user_avg 1 1 4.5 4.2 1 2 4.2 4.2 1 6 3.9 4.2 2 2 4.7 4.4 2 7 4.6 4.4 2 8 3.9 4.4 3 1 2.9 3.9 3 2 4.9 3.9 3 6 NULL 3.9 or keep every row the same 9 Listens rows, plus a user_avg column The same per-user average, two ways. GROUP BY collapses the nine listen rows into one summary row per user (Mickey 4.2, Minnie 4.4, Daffy 3.9), losing the detail. A window function, AVG(rating) OVER (PARTITION BY user_id), keeps all nine rows and attaches each user's average as a new column. Same math, but the window preserves row granularity.
Notes ↗ window-functions · Ranking Functions RANK in three steps: partition, order, assign 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 sorts each lane with NULL last, and the rank column numbers each lane from one. RANK in three steps: partition, order, number The same query expansion, side by side. Each clause does one thing. -- Rank each user's listens by rating, highest first. SELECT user_id, song_id, rating, RANK() OVER (PARTITION BY user_id ORDER BY rating DESC) AS rank FROM Listens Step 1: PARTITION BY user_id user_id rating 1 4.5 1 4.2 1 3.9 2 4.7 2 4.6 2 3.9 3 2.9 3 4.9 3 NULL three lanes, as-is Step 2: ORDER BY rating DESC user_id rating 1 4.5 1 4.2 1 3.9 2 4.7 2 4.6 2 3.9 3 4.9 3 2.9 3 NULL ordered: user 1 ordered: user 2 ordered: user 3 each user sorted on its own, NULL last Step 3: assign the rank user_id rating rank 1 4.5 1 1 4.2 2 1 3.9 3 2 4.7 1 2 4.6 2 2 3.9 3 3 4.9 1 3 2.9 2 3 NULL 3 top = 1, then 2, then 3 per lane Notes ↗ window-functions · Ranking Functions ROW_NUMBER, RANK and DENSE_RANK on a tie 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). Three ways to rank, when there is a tie Mickey's listens by rating, plus a hypothetical second 4.5. The functions agree until the tie. rating 4.5 4.5 4.2 3.9 ROW_NUMBER 1 2 3 4 never ties RANK 1 1 3 4 shares 1, then SKIPS to 3 (a gap) DENSE_RANK 1 1 2 3 shares 1, stays PACKED at 2 (no gap)
The three ranking functions agree until a tie. ROW_NUMBER is always unique (1, 2, 3, 4). RANK lets the tie share a rank then skips, leaving a gap (1, 1, 3, 4). DENSE_RANK lets the tie share a rank but stays packed, no gap (1, 1, 2, 3). The shaded band is Mickey's hypothetical 4.5 tie.
Notes ↗ end of lecture 1 ~70 min of figures
window-functions · Key Rules The OVER clause makes a function a window function The OVER clause makes a function a window function AVG(rating) collapses like GROUP BY. AVG(rating) OVER (PARTITION BY user_id) is a window function: one value per row, all rows kept. Any aggregate plus LAG and LEAD work in a window. What makes it a window? The OVER clause. The same aggregate collapses without OVER, and keeps every row with it. AVG(rating) GROUP BY style collapses to one row per group AVG(rating) OVER (PARTITION BY user_id) window function one value per row, all rows kept Any aggregate works in a window, plus LAG and LEAD to see the previous or next row.
Notes ↗ window-functions · Common Patterns Five common window-function patterns Five common window-function patterns Ranking, running totals with SUM OVER ORDER BY, moving averages with a ROWS window, LAG and LEAD, and NTILE percentiles. Window functions go well beyond ranking The same OVER skeleton, five everyday shapes. Ranking order rows within a group ROW_NUMBER() / RANK() / DENSE_RANK() Running total accumulate over time SUM(x) OVER (ORDER BY date) Moving average smooth a sliding window AVG(x) OVER (ROWS BETWEEN ...) Lead / Lag compare to the previous or next row LAG(x) / LEAD(x) Percentiles bucket rows into quantiles NTILE(4) / PERCENT_RANK()
Notes ↗ window-functions · Common Mistakes Three common window-function mistakes Three common window-function mistakes Missing PARTITION BY ranks the whole table; wrong ORDER BY direction flips the ranking; NULL ordering varies by database. Three ways window queries go wrong All three are silent: the query runs, the answer is just wrong. ! Missing PARTITION BY Leave it out and you rank across the WHOLE table, not per group. One global #1, not one per user. ! Wrong ORDER BY direction ASC vs DESC flips the ranking. Highest-first needs DESC, or your #1 is your worst row. ! Forgetting NULLs in ORDER BY NULLs sort first or last depending on the database (Postgres first, MySQL last). Be explicit.
Notes ↗ M1 Writing Debug Tables: Hand-Tracing a Query Debug tables, also called handtraces, validate a query's logic by walking the input rows through each clause and writing down what survives at each step.
3 figures writing-debug-tables · Why This Matters Why hand-trace a query Why hand-trace a query LLMs miss semantics; interviews ask if a query is correct; a 5-minute trace beats a 30-minute re-prompt loop. Why hand-trace at all? A syntax check passes a wrong query. A trace catches it. LLMs miss semantics Syntax: 95%+ correct. Semantics: 16-77% (BIRD). A query can parse and still be logically wrong. Interviews and tests "Is this query correct?" is the classic question. Reading the logic is what gets you hired. It is faster Re-prompting an LLM: ~30 min. Drawing one debug table: ~5. And you build real SQL intuition along the way.
Notes ↗ writing-debug-tables · Example 1: Users' Genres with 2+ Songs Hand-trace of the GROUP BY / HAVING query 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, not the 4 the query claimed. Hand-trace the query: rows in, survivors out The query claims 4 user-genre pairs. Walk each clause and see what really survives. -- Find user-genre pairs where the user listened to >= 2 songs in that genre. SELECT u.user_id, s.genre, COUNT(*) AS song_count FROM Users u JOIN Listens l ON l.user_id = u.user_id JOIN Songs s ON s.song_id = l.song_id GROUP BY u.user_id, s.genre HAVING COUNT(*) >= 2 Step 1 · FROM + JOIN 9 rows, every listen with its genre user genre rating 1 Pop 4.5 1 Pop 4.2 1 Classic 3.9 2 Pop 4.7 2 Classic 4.6 2 Classic 3.9 3 Pop 2.9 3 Pop 4.9 3 Classic NULL GROUP BY Step 2 · GROUP BY 9 rows -> 6 user-genre groups user genre count 1 Pop 2 1 Classic 1 2 Pop 1 2 Classic 2 3 Pop 2 3 Classic 1 HAVING Step 3 · HAVING COUNT(*) >= 2 6 groups -> 3 survive (not 4) user genre count 1 Pop 2 ✓ 1 Classic 1 2 Pop 1 2 Classic 2 ✓ 3 Pop 2 ✓ 3 Classic 1 Answer: 3 pairs survive, not the 4 claimed. Notes ↗ writing-debug-tables · Quick Verification Checklist A five-point query checklist A five-point query checklist Check JOIN row counts, NULL handling, group granularity, aggregate choice, and window partitions. Five things to check on every trace Run down the list; each one is a place real queries go wrong. ✓ JOINs Expected row count? Not accidentally cartesian? INNER vs LEFT vs OUTER? ✓ NULLs Handled correctly? IS NULL, not = NULL? NOT IN guarded against NULLs? ✓ Groups Right granularity? Grouped by user, or by user and genre? ✓ Aggregates Makes sense? COUNT(*) counts rows; COUNT(column) skips NULLs. ✓ Windows Partitions correct? RANK vs ROW_NUMBER vs DENSE_RANK on ties?
Notes ↗ M1 Query Equivalence Two queries are equivalent if they return the same result for any input data.
6 figures sql-query-equivalence · Important: Query Equivalence vs Output Equivalence Query equivalence vs output equivalence Two queries can agree today and disagree tomorrow That is the difference between output equivalence and real query equivalence. Query equivalence Same result for ANY input. WHERE x BETWEEN 1 AND 5 WHERE x >= 1 AND x <= 5 Output equivalence Same result for THIS data only. WHERE rating > 4.5 WHERE rating >= 4.6 Tomorrow a row rates 4.55: > 4.5 keeps it, >= 4.6 drops it. They split. A coincidence. Any x, any table: the two are identical. Always the same. A guarantee. Query equivalence vs output equivalence Query equivalence holds for any input and is a guarantee; output equivalence holds only for the current data and is a coincidence that can break on a new row: WHERE rating greater than 4.5 and rating at least 4.6 agree until a row rates 4.55, then they split.
Notes ↗ sql-query-equivalence · Example 1: Users Above Their Personal Average Two shapes, one answer: users above their personal average Two shapes, one answer: users above their personal average Both compute the same value, this user's average rating, then keep anyone who beats it. Correlated subquery SELECT DISTINCT u.name FROM Users u JOIN Listens l ON u.user_id = l.user_id -- this user's avg, recomputed per row WHERE l.rating > ( SELECT AVG(l2.rating) FROM Listens l2 WHERE l2.user_id = u.user_id) CTE + JOIN WITH UserAvgs AS ( -- this user's avg, computed once SELECT user_id, AVG(rating) AS avg FROM Listens GROUP BY user_id) SELECT DISTINCT u.name FROM Users u JOIN Listens l ON u.user_id = l.user_id JOIN UserAvgs ua ON ua.user_id = u.user_id WHERE l.rating > ua.avg computes each user's average keeps rows above it both queries return the same rows, for any input name Mickey Minnie Daffy Two shapes, one answer: users above their personal average Both compute the same value, this user's average rating, then keep anyone who beats it. They return the same rows.
Notes ↗ sql-query-equivalence · Example 2: Users Who Listen to Multiple Genres Two shapes, one answer: users who span at least 2 genres Two shapes, one answer: users who span at least 2 genres Two operations: count distinct genres per user, then keep 2 or more. HAVING fuses them; the CTE names the count, then filters. JOINs and GROUP BY are shared scaffolding. GROUP BY + HAVING SELECT u.name FROM Users u JOIN Listens l ON u.user_id = l.user_id JOIN Songs s ON l.song_id = s.song_id GROUP BY u.user_id, u.name -- one clause does both: count, then keep >= 2 HAVING COUNT(DISTINCT s.genre) >= 2 CTE for clarity WITH UserGenreCounts AS ( -- name the distinct-genre count SELECT l.user_id, COUNT(DISTINCT s.genre) AS genre_count FROM Listens l JOIN Songs s ON l.song_id = s.song_id GROUP BY l.user_id) SELECT u.name FROM Users u JOIN UserGenreCounts ugc ON ugc.user_id = u.user_id WHERE ugc.genre_count >= 2 counts distinct genres per user keeps users with 2 or more both queries return the same rows, for any input name Mickey Minnie Daffy Two shapes, one answer: users who span at least 2 genres Two operations: count distinct genres per user, then keep 2 or more. HAVING fuses them; the CTE names the count, then filters. JOINs and GROUP BY are shared scaffolding. They return the same rows.
Notes ↗ sql-query-equivalence · Example 3: Top 3 Songs per User Top 3 per user: output-equivalent but not the same query Output-equivalent, not the same query: each user's top 3 songs Both rank a song within its user. On distinct ratings they agree; one extra tied song splits them. Window: ROW_NUMBER() <= 3 WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_id -- per user ORDER BY rating DESC) AS rn -- highest first FROM Listens WHERE rating IS NOT NULL ) SELECT * FROM ranked WHERE rn <= 3 -- top 3 Subquery: count how many rate higher SELECT * FROM Listens l WHERE l.rating IS NOT NULL AND ( SELECT COUNT(*) FROM Listens l2 WHERE l2.user_id = l.user_id AND l2.rating > l.rating -- count higher ) < 3 -- fewer than 3 rate higher Mickey's songs by rating: rating ROW_NUMBER songs rated higher A: rn <= 3 B: # higher < 3 4.5 1 0 ✓ ✓ 4.2 2 1 ✓ ✓ 3.9 3 2 ✓ ✓ On distinct ratings, both queries keep all three. They agree. 3.9 4 2 ✗ ✓ ← the extra tuple Add one more rated 3.9, tying for 3rd: ROW_NUMBER <= 3 keeps 3, COUNT(higher) < 3 keeps 4. Rank 4 to ROW_NUMBER (dropped), but still only 2 rate higher (kept). The tie at the cutoff is where they split. Top 3 per user: output-equivalent but not the same query 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. Add one more song rated 3.9, tying for third: ROW_NUMBER assigns it rank 4 and drops it (3 rows), but only 2 songs rate higher than 3.9 so the subquery keeps it (4 rows). The tie at the cutoff is where they diverge.
Notes ↗ sql-query-equivalence · Where the Look-Alikes Split Where the look-alike queries split Where the look-alikes split Equal on our nine rows. Change the data, or the schema, and they come apart. A tie breaks Example 3 One user, four songs tied at 5.0: rating row_number # higher rn<=3 #h<3 5.0 1 0 ✓ ✓ 5.0 2 0 ✓ ✓ 5.0 3 0 ✓ ✓ 5.0 4 0 ✗ ✓ ROW_NUMBER keeps 3 · COUNT(higher) keeps 4 A NULL breaks NOT IN Exclude-list = { 2, NULL }: user_id NOT IN (2, NULL) NOT EXISTS 1 UNKNOWN → drop keep 3 UNKNOWN → drop keep 4 UNKNOWN → drop keep NOT IN → nothing · NOT EXISTS → 1, 3, 4 Where the look-alike queries split Two concrete divergences. With four songs tied at 5.0, ROW_NUMBER keeps 3 while COUNT of higher-rated keeps 4. With a NULL in the exclude-list, NOT IN returns nothing while NOT EXISTS returns the right rows; on this schema user_id is NOT NULL so it cannot happen here.
Notes ↗ sql-query-equivalence · The Takeaway Same answer today is not the same query Same answer today is not the same query Real equivalence holds for every future input, not just the rows you can see today. 1 Holds for ANY input Equivalent means they agree on every possible table, not only the one in front of you. 2 Edges are where they split NULLs, ties, and boundary values are exactly where look-alike queries quietly diverge. 3 Read first, trust the optimizer When the logic matches, pick the clearest shape and let the optimizer make it fast. Same answer today is not the same query Real query equivalence holds for any input: equivalent queries agree on every possible table; NULLs, ties, and edge values are where look-alike queries diverge; when the logic matches, pick the clearest shape and trust the optimizer.
Notes ↗ M1 Schema Design: One Fact, One Place Schema design decides which tables hold which columns.
3 figures schema-design · The problem: one big table repeats itself One big table repeats itself
One big 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.
One big table repeats itself
Put every column in one table and the same facts appear over and over.
Listens, one wide table
listen_id
name
email
title
artist
rating
1 Mickey mickey@ex.com Evermore Taylor Swift 4.5
2 Mickey mickey@ex.com Willow Taylor Swift 4.2
3 Mickey mickey@ex.com Yesterday Beatles 3.9
7 Daffy daffy@ex.com Evermore Taylor Swift 2.9
8 Daffy daffy@ex.com Willow Taylor Swift 4.9
Mickey's email, copied on all 3 rows
one artist, copied again and again
What repetition costs you
Update
Mickey changes his email. Fix every Mickey row,
miss one, and now one person has two emails.
Insert
Add a brand-new song nobody has played yet.
There is no listen row to hang it on.
Delete
Delete Mickey's last listen and his email
disappears with it. The fact had nowhere else to live.
Color key amber = a fact copied many times · red = the anomalies that repetition causes.
Repeating a fact across rows creates update, insert, and delete anomalies. An update to Mickey's email changes many rows and can leave different copies of the same fact. An insert cannot store a song fact until some listen row exists. A delete can remove a fact when the last row carrying it goes.
Notes ↗ schema-design · The fix: give each fact one home Give each fact one home
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.
Give each fact one home
Each fact is stored once. Listens points to it by key.
Users
user_id (PK) · name · email1 Mickey mickey@ex.com
3 Daffy daffy@ex.com
Mickey's email lives here once
Songs
song_id (PK) · title · artist · genre1 Evermore Taylor Swift
2 Willow Taylor Swift
Taylor Swift lives here once
Listens
listen_id (PK)user_id (FK) · song_id (FK) · ratingjust the events, pointing by key
Color key green = the normalized design · arrows are foreign keys pointing at the one place each fact lives.
Normalization stores each fact once and connects tables with keys. Boyce-Codd Normal Form (BCNF) gives a formal definition of this goal. Updating Mickey's email changes one row in Users, and joins pull that value into query results. A foreign key, like `Listens.user_id`, points to the row that stores the user fact.
Notes ↗ schema-design · The trade-off: normalize first, denormalize only when forced Normalize first. Denormalize only when forced.
Normalize first. Denormalize only when forced.
Two panels. Left, green, Normalized: each fact once, no anomalies, recombine with a JOIN; the cost is a join at read time. Right, amber, Denormalized: copy facts back together for fast reads with no join, accepting the risk that the copies drift apart; used for read-heavy analytics at scale. Color key: green is the safe default, amber is the deliberate exception you take only when a measured read cost forces it.
Normalize first. Denormalize only when forced.
Same data, two layouts, opposite trade-offs.
Normalized (the default)
Each fact stored once. No update, insert,
or delete anomalies.
Cost: you JOIN to recombine at read time.
safe by default; correctness first
Denormalized (the exception)
Copy facts back together so a read needs
no join. Faster reads.
Cost: the copies can drift apart.
used for read-heavy analytics at scale
Color key green = the safe default · amber = the deliberate exception, taken only when a measured read cost forces it.
Normalization avoids anomalies and requires joins during reads. Denormalization copies facts to reduce join cost and can create inconsistent copies. Start with a normalized schema and denormalize after measuring a read bottleneck. Module 5 uses intentional copying to serve millions of reads.
Notes ↗ M1 How a Query Runs A database engine compiles SQL text into an execution plan.
3 figures parallel-execution · The Challenge The same query on one machine or a hundred, returning the same answer Same query, a billion times the data Scale up by a factor of a billion without rewriting one line of SQL. SQLite, one machine Single-threaded, a small table: 9 rows on your laptop one core, start to finish Runs the whole query alone. BigQuery, ~100 machines The same SQL, distributed: 5.5 TB across the cluster each machine takes a slice Same answer, back in milliseconds. Same algorithm. Only the data layout changes. The same query on one machine or a hundred, returning the same answer The same SQL query runs on one SQLite machine over a tiny table, or across about 100 BigQuery machines over 5.5 TB, returning the same answer in milliseconds. Notes ↗ parallel-execution · The Journey: From Text to Results in 47ms One query, 5.5 TB, about 100 machines, about 47 ms
One query, 5.5 TB, about 100 machines, about 47 ms
A left-to-right pipeline. A SQL query is parsed, turned into a query plan, then a coordinator splits 5.5 TB into shards and fans the work out to roughly 100 machines that each scan one shard in parallel; the partial results merge into the top 10 in about 47 ms. Color key: grey is a pipeline stage, blue is the roughly 100-machine engine in focus, and the ink target marks the takeaway. It is the same algorithm SQLite runs on 9 rows, only split across machines.
One query · 5.5 TB · ~100 machines · ~47 ms
Top 10 most-played songs since 2024, the same algorithm SQLite runs on 9 rows, only split across machines.
SQL query
SELECT s.title, COUNT(*)
FROM songs s JOIN listens l
WHERE l.ts >= '2024-01-01'
GROUP BY s.title
ORDER BY 2 DESC LIMIT 10
Parse · 0–5 ms
Query plan
operator tree:
scan → join →
group → sort
Plan · 5–12 ms
Coordinator
splits 5.5 TB into
~100 shards (~55 GB)
Distribute · 12–15 ms
≈ 100 machines, in parallel
scan shard
scan shard
scan shard
scan shard
⋯
scan shard
each runs the same scan + group on ~55 GB
Execute · 15–45 ms
Result
top 10
songs
→ 47 ms
Color key grey = a pipeline stage · blue = the ~100-machine engine in focus
One SQL query over 5.5 TB runs in about 47 ms on about 100 machines. The engine parses the query into a plan. A coordinator shards the data, runs the plan on each shard in parallel, and merges partial results into the final top 10.
Notes ↗ parallel-execution · What You're Seeing One SQL statement, one result; underneath, shards running in parallel One statement you write, a choreography that runs The abstraction holds because the algorithms are the same. What you write one SQL statement SELECT ... GROUP BY ... LIMIT 10 one result top 10 songs What actually runs split the table by hash key 100 shards run in parallel shuffle + merge combine partials result global top 10 One SQL statement, one result; underneath, shards running in parallel A developer writes one SQL statement and gets one result; underneath, the engine splits the table into shards, runs them in parallel, then shuffles and merges the partial results. Notes ↗ M1 SQL Libraries: Define Once, Compose A library packages the SQL you repeat as named, parameterized queries, so you define a term once and compose it everywhere.
1 figure sql-libraries · What a Library Spans A small vocabulary spans a large answerable domain
A small vocabulary spans a large answerable domain
Three white panels with accent top-strips. 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.
A small vocabulary spans a large answerable domain
Define a few parameterized primitives. Compose them to answer the rest.
DEFINED
the vocabulary you write
listener_of(artist)
active_since(days)
liked(min_rating)
each pins a choice
UTC midnight, not 24h;
a NULL rating is not a like
dimensions: artist, genre,
window, rating
ANSWERABLE
compose with ∩ − ∪, none of it written
All − listener_of('Taylor')
active_since(7) ∩ listener_of('Beatles')
listener_of('Taylor') ∩ liked(4.0)
power_user(min)
add power_user, and a new question composes:
power_user(50) ∩ listener_of('Taylor')
OFF THE TABLE
no definition reaches here
which songs are good?
subjective
why did Pluto quit?
data you don't have
will Daffy churn?
a prediction, not a lookup
Curation grows the green by adding to the blue. The grey is the edge of what any definition can reach.
A few defined terms (blue) compose into a large answerable domain (green), none of those queries written out. Add one term (power_user) and a new composition appears: the domain grows by adding to the library, not by anything in the answerable set. Past a hard boundary is what no definition can reach (grey): subjective, missing-data, or predictive questions.
Notes ↗ M1 Validating SQL: Prove the Result A query that runs is not a query that is right.
4 figures validating-sql · Unit Test: One Query Query A, the LEFT JOIN, traced Query A, the LEFT JOIN, traced Keep every play that is not Taylor (or has no song), then group by user. SELECT u.name FROM Users u LEFT JOIN Listens l ON u.user_id = l.user_id LEFT JOIN Songs s ON l.song_id = s.song_id WHERE s.artist != 'Taylor Swift' OR s.artist IS NULL GROUP BY u.user_id, u.name After the joins every play, with its artist name song artist Mickey Evermore Taylor Mickey Willow Taylor Mickey Yesterday Beatles Minnie Willow Taylor Minnie Yellow Sub Beatles Minnie Hey Jude Beatles Daffy Evermore Taylor Daffy Willow Taylor Daffy Yesterday Beatles Pluto NULL NULL WHERE: drop the Taylor plays Mickey Evermore Taylor Mickey Willow Taylor ✓ Minnie Willow Taylor ✓ ✓ Daffy Evermore Taylor Daffy Willow Taylor ✓ ✓ GROUP BY: result 4 users name Mickey Minnie Daffy Pluto All 4 users survive, even the Taylor listeners. That is the bug. Query A, the LEFT JOIN, traced Keep every play that is not Taylor (or has no song), then group by user. Dropped rows are greyed and struck through; survivors carry a green check. Color key: grey strike = a row the clause drops, green check = a row that survives. Two executable readings traced clause by clause. Query A (the raw LEFT JOIN) removes the Taylor rows, but each user keeps another surviving row, so GROUP BY returns all four users. Query B (calling listener_of) removes the three Taylor listeners and returns only Pluto. A unit test resolves the ambiguity by checking the returned rows.
Notes ↗ validating-sql · Unit Test: One Query Query B, the NOT IN, traced Query B, the NOT IN, traced Find everyone who heard Taylor, then exclude exactly them. SELECT u.name FROM Users u WHERE u.user_id NOT IN ( SELECT user_id FROM listener_of('Taylor Swift') ) Every user, did they hear Taylor? who listener_of returns name heard Taylor? Mickey yes Minnie yes Daffy yes Pluto no NOT IN: drop the Taylor listeners Mickey yes Minnie yes Daffy yes ✓ Result 1 user name Pluto Only Pluto remains, exactly what the spec asked for. Query B, the NOT IN, traced Find everyone who heard Taylor, then exclude exactly them. Dropped rows are greyed and struck through; survivors carry a green check. Color key: grey strike = a row the clause drops, green check = a row that survives. Two executable readings traced clause by clause. Query A (the raw LEFT JOIN) removes the Taylor rows, but each user keeps another surviving row, so GROUP BY returns all four users. Query B (calling listener_of) removes the three Taylor listeners and returns only Pluto. A unit test resolves the ambiguity by checking the returned rows.
Notes ↗ validating-sql · Regression: The Eval Set One test today, a suite that holds over time
One test today, a suite that holds over time
A left-to-right timeline of validation. First, one unit test proves a single query today: non-Taylor listeners return Pluto, with a green check. Next, you keep it and the eval set grows into a stack of verified tests. Then you re-run the whole suite on every change over time, drawn as a grid of runs by date: mostly green checks, until a definition drifts and one test fails red that day. Color key: blue marks the three stages and the time axis; a green check is a passing test, a red cross is the test that catches the drift the day it happens.
One test today, a suite that holds over time
A unit test proves one query now. Keep it, grow the set, re-run it: a drift fails the day it happens.
time
1 · Unit test
prove one query today
non-Taylor listeners
expect: Pluto
✓
day 0
2 · Eval set
keep it, add more
non-Taylor listeners → Pluto
✓
Taylor listeners → 3 users
✓
active this week → 2 users
✓
as you build
3 · Re-run over time
every change re-runs the suite
v1
v2
v3
v4
v5
A
✓
✓
✓
✓
✓
B
✓
✓
✓
✗
✓
C
✓
✓
✓
✓
✓
a definition drifts at v4
test B fails, that day
every change
blue = the three stages and the time axis · green = a passing test · red = the drift caught
Validation over time. A unit test proves one query today (left); you keep it, so the eval set grows into a suite of verified questions (middle); then every change re-runs the whole suite (right), and a definition that drifts fails a test the day it drifts, not months later. The eval set answers "does the library still work" with a number instead of a hope.
Notes ↗ validating-sql · Verified Queries Compose Verified blocks compose into a library
Verified blocks compose into a library
Three columns of SQL blocks (blue), each marked verified with
a green check. Base metrics on the left (active user, Taylor listener) feed
composed queries in the middle (weekly actives, non-Taylor listeners), which
feed a complex query on the right (weekly active non-Taylor listeners). Arrows
run left to right showing composition: each query is built from blocks already
checked, so a new query only adds its own layer to verify. Color key: blue is
a SQL block in the library, the green check is verified once and then reused,
arrows are composition.
Verified blocks compose into a library
Check each query once. Bigger queries build on blocks already verified.
Base metrics
✓
active user
✓
Taylor listener
Composed
✓
weekly actives
✓
non-Taylor listeners
Complex query
✓
weekly active
non-Taylor listeners
blue = a SQL block in the library · ✓ = verified once, then reused · arrows = composition
Each block is a verified query (blue, green check). Base metrics compose into bigger queries, which compose into a complex one. Every block is verified once and reused, so a new query verifies only its own layer. A library of verified SQL is worth more than any single query: you build on it instead of re-proving it.
Notes ↗ M1 Why Learn SQL When an LLM Can Draft It? The model writes the SQL in a second.
2 figures llm-debug · One English Sentence Hides Sixteen Queries One English question hides sixteen valid SQL queries, and different queries return different answers
One English question hides sixteen valid SQL queries, and different queries return different answers
Left: one English sentence, find users who do not listen to Taylor Swift. Middle: three independent decisions the sentence leaves open (how to match Taylor Swift, what counts as a listen, and whether a user who plays nothing counts), multiplying to 2 times 4 times 2 equals 16. Right: a grid of 16 query cells, every one a valid query that runs and returns some answer. Two are labelled to show how far apart the answers are: one returns just Pluto, another returns all four users. An orange ring marks the square the model happened to land on this run; its pick is not fixed and can move to another square on another run or a newer model. Color key: grey is a valid query, all sixteen of which run, and orange is the one the model happened to pick.
One Question, Sixteen Queries
Each decision you leave open multiplies. Different queries return different answers.
"Find users who don't
listen to Taylor Swift"
one English sentence
Every open decision multiplies
match Taylor Swift: exact · + collabs ×2
“listen” means: any play · repeat · recent · favourite ×4
a user who plays nothing: counts · excluded ×2
2 × 4 × 2 = 16 queries
16 queries, every one runs
this one returns
just Pluto
this one returns
all four
the model’s pick
not fixed, run to run
Settle every decision, or the model settles it for you.
Color key grey = all sixteen run, and they disagree · orange = the one the model happened to pick Sixteen valid queries behind one sentence. They do not agree: one returns a single user, another returns all four. Nothing in the grid marks which one you wanted, because nothing in the question does. The model picks a square (the orange ring) and hands you the SQL, so which one it picked is readable, if you can read SQL. Whether it is the one you meant is a separate question, and asking again tomorrow, on a newer model, or from a teammate’s seat can move the square. And this is the smallest schema you will ever see.
Notes ↗ llm-debug · The Benchmark Says 80%. A Real Warehouse Says 30%. BIRD, the industry-standard text-to-SQL benchmark: who competes and the numbers
BIRD, the industry-standard text-to-SQL benchmark: who competes and the numbers
BIRD, the industry-standard text-to-SQL benchmark. Every serious lab competes on it: OpenAI, Anthropic, Google, Databricks, Snowflake, Amazon, Alibaba, Tencent, ByteDance and Huawei. It runs 12,751 questions over 95 real databases, 33 GB of schemas, across 37 domains. The best systems reach about 80% on BIRD but only about 30% on BEAVER, a benchmark built from real company warehouses. Same labs and same models on both; the difference is the context supplied with the question.
BIRD · the text-to-SQL benchmark
Every serious lab competes on it, on real databases.
Who competes
OpenAI
Anthropic
Google
Databricks
Snowflake
Amazon
Alibaba
Tencent
ByteDance
Huawei
The benchmark
12,751
questions
95 databases
33 GB of real schemas
37 domains
healthcare to blockchain
Best systems, two benchmarks
~80%
BIRD
public benchmark
~30%
BEAVER
real company warehouses
Same labs, same models.
The gap is the context you supply.
BIRD uses 12,751 questions over 95 real databases, 33 GB, across 37 domains. The best systems reach about 80% execution accuracy; humans reach 93%. Each question includes an evidence string that supplies the needed definitions. The score measures SQL generation given a supplied meaning.
Notes ↗ M1 Case Study 1.1: Anthropic's Self-Service Analytics Stack English is too vague to hand a model, so somebody has to write the meaning down somewhere precise.
4 figures case-study-agentic-data-stack · What They Actually Built The loop, as Anthropic actually built it
The loop, as Anthropic actually built it
The same two tiers, with each part replaced by the infrastructure
Anthropic runs. The top tier is what happens for one question, left to right: a skill
that routes the model to the roughly thirty documents defining that domain, then Claude
in grey which only generates, then their warehouse, which the model queries and retries
against directly, then a suite of evals that gate it. The bare model already had the warehouse and
scored twenty one percent. The bottom tier is the governed semantic layer, drawn lower
and wider as a base that only grows: whatever clears the evals becomes a definition in
it, and an arrow runs from the layer back up into the skill, so it is the context for
the next question. Same four parts, same model, twenty one percent to about ninety
five. Color key: blue is a part they built, grey is the commodity.
The loop, as Anthropic actually built it
Four parts around a model that only generates. 21% → ~95% , and near 99% in some domains.
ONCE PER QUESTION
Claude
writes the SQL
the commodity;
swap it, this survives
1
Their warehouse
it queries and retries
against the real thing
the bare model already had
this, and scored 21% runs and retries on its own
2
The skill
routes it to the ~30 docs
defining this domain
the mandatory default
path, not a suggestion
3
Evals
one suite per domain,
run before anyone trusts it
a domain ships to production
only once it clears ~90%
ACCUMULATES ACROSS QUESTIONS
4
The semantic layer
the memory of this loop
whatever clears the evals becomes a definition
governed, verified, and it only grows
what passes
is kept
and becomes
the context for
the next question
Handing it the raw query history instead moved accuracy less than one point.
Four parts around a model that only generates. Their warehouse is part one, and the bare model already had it, which is why 21% is the honest starting number. The skill that loads a domain's ~30 definition docs is part two, and it is the mandatory default path. Evals are part three, and a domain reaches production only once it clears ~90%. The semantic layer is the lower tier: whatever clears the evals becomes a definition in it, and it feeds back up as the context for the next question.
Notes ↗ case-study-agentic-data-stack · How to Start on Your Own Data Two ways to make the question precise
Two ways to make the question precise
On the left, the vague question, users who don't listen to Taylor Swift,
with three decisions left open and sixteen queries. It forks two ways. The top branch,
in grey, spells every decision out in the prompt: it answers this one question, and the
next question means writing the whole spec again. The bottom branch, in blue,
operationalises the term instead, shown as three numbered steps: define it once, call it
by name, set up a unit test. That one is written once and answers every later question
that mentions listening. Color key: grey is the option you repeat, blue is the option you
keep.
Two ways to make it precise
Both of them work. Only one of them you do once.
The question
users who don’t listen
to Taylor Swift
three decisions open · 16 queries
Spell it out in the prompt
answers this one question
match: artist = 'Taylor Swift'
listen: any row in Listens
empty: zero listens is NOT a listener
then write all of it again for the next question
Operationalize it
answers every question that mentions it
1
Define it once
one SQL function
2
Call it by name
the question composes
3
Set up a unit test
one per decision
Written once. Every later question about listening calls the same term.
Same three decisions either way. The prompt restates them; the function keeps them.
Both branches are precise. Only one of them you do once. The same three decisions get settled either way; the difference is whether they survive the question that settled them. The three numbered steps are the whole job, and the next section does each one in SQL.
Notes ↗ case-study-agentic-data-stack · How to Start on Your Own Data One term, three beats: define it, call it, test it
One term, three beats: define it, call it, test it
Three stacked panels read top to bottom. First, in blue, the definition:
listener_of is created once as an ordinary SQL function that takes an artist and returns
the set of user ids who listened. Second, the usage: the original question is rewritten to
call listener_of by name instead of spelling the joins out again. Third, the test: calling
listener_of for Taylor Swift returns Mickey, Minnie and Daffy, and Pluto, who played
nothing, is absent. A green check marks the test passing. Color key: blue is the term you
define and reuse, green is the test that holds it in place.
One term, three beats
Define it once. Call it by name. Set up a unit test. Everything after this is composition.
1
Define it once
an ordinary SQL function
CREATE FUNCTION listener_of(artist TEXT)
RETURNS TABLE (user_id INT) AS $$
SELECT DISTINCT l.user_id
FROM Listens l JOIN Songs s ON l.song_id = s.song_id
WHERE s.artist = listener_of.artist
$$ LANGUAGE sql STABLE;
2
Call it by name
and every later question that mentions listening calls the same term
SELECT name FROM Users
WHERE user_id NOT IN (SELECT user_id FROM listener_of('Taylor Swift'));
3
Set up a unit test
one test per decision the term settles
SELECT * FROM listener_of('Taylor Swift');
--> Mickey, Minnie, Daffy ...
Pluto played nothing, so he is not there.
The definition fixes decisions that later queries would otherwise repeat. DISTINCT chooses how repeat plays count. The JOIN chooses what a listen means. A user with no listens never appears, so Pluto does not qualify as a listener. STABLE lets Postgres inline the function; without it Postgres plans the function as a black box and rebuilds it on every call. Later queries compose functions, and the model calls the term instead of inventing a definition.
Notes ↗ case-study-agentic-data-stack · How to Start on Your Own Data What survives when you swap the model
What survives when you swap the model
The library builds first, along the bottom, growing from three verified functions to
nine to twenty-two against a common baseline. Only then does the top row appear: the
model underneath was replaced three times while that happened, v1 to v2 to v3, each swap
a configuration change. A panel on the right lists what is not model-specific: the
database it runs on, the specs, the tests, and the named functions. Each
stage of the library carries its own test count, seven then twenty-one then fifty-four rows,
because every axis a concept names costs one row. Nothing in the library is re-verified
when the model changes; the suite is instead what every new model's SQL has to pass, so
the meaning cannot drift underneath you.
The closing line is that the model is rented and the library is owned. Color key: blue
is the piece that accumulates, grey is the piece you replace.
What survives when you swap the model
The library only grows. The model underneath it gets replaced, and none of that touches the library.
YOUR LIBRARY · ACCUMULATED
3 functions
7 tests
you start here
9 functions
21 tests
each new term brings its own
22 functions
54 tests
and still growing
THE MODEL · REPLACED, MEANWHILE
model v1
model v2
model v3
swapped twice while that happened. A config change each time.
None of it is model-specific
· the database it runs on
· the specs
· the tests · the named functions
The model is rented. The library is owned.
The suite is what holds the meaning still. Every swap above has to pass the same tests.
The model is rented; the library is owned. New versions land every few weeks and swapping one is a config change. Nothing underneath is re-verified, because none of what makes an answer trustworthy depends on which model produced it.
Notes ↗ M1 Case Study 1.2: How ChatGPT Stores Its Chats The world's most-used AI product runs on SQL.
2 figures case-study-openai-postgres · The Read-Heavy Constraint One ChatGPT question: one write, then hundreds of reads One ChatGPT question: one write, then hundreds of reads One ChatGPT question is one write, orange, to save the new message, then hundreds of reads, blue, to fetch the history and build context. Color key: orange = the single write, blue = the many reads, the bottleneck. One question: one write, then hundreds of reads Every ChatGPT turn saves one message, then re-reads the whole conversation for context. 1 WRITE save the new message then HUNDREDS OF READS fetch history, load the sidebar, build the LLM context Reads are the bottleneck, not writes. So scale reads. Notes ↗ case-study-openai-postgres · The Read-Heavy Constraint One writable primary, about fifty read replicas serving every read One writable primary, about fifty read replicas serving every read Writes, orange, go to one writable primary that holds the truth with ACID; the primary replicates to about 50 read replicas, blue, and all read traffic fans out to them. Color key: orange = the single writable primary, blue = the read replicas. One primary for the truth, replicas for the reads Writes stay on a single primary; 50 read replicas absorb the query load. PRIMARY one writable copy holds the truth (ACID) Writes new messages write read replica read replica read replica ... 50 globally distributed replicas replicate Reads history, sidebars reads fan out to the replicas Notes ↗ M1 SQL Problem Solving: Reading Complex Queries Read a query in execution order: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY.
2 figures sql-reading-queries · Example 1: Finding Top Genres Per User Example 1: watch the table shrink Example 1: watch the table shrink Read in execution order: FROM and JOIN, then GROUP BY, then HAVING. SELECT user_id, genre, COUNT(*) AS song_count FROM Listens l JOIN Songs s ON l.song_id = s.song_id WHERE genre IS NOT NULL GROUP BY user_id, genre HAVING COUNT(*) >= 2 FROM + JOIN 9 rows, each with its genre name song genre Mickey Evermore Pop Mickey Willow Pop Mickey Yesterday Classic Minnie Willow Pop Minnie Yellow Sub Classic Minnie Hey Jude Classic Daffy Evermore Pop Daffy Willow Pop Daffy Yesterday Classic group GROUP BY 9 rows -> 6 user-genre groups name genre count Mickey Pop 2 Mickey Classic 1 Minnie Pop 1 Minnie Classic 2 Daffy Pop 2 Daffy Classic 1 filter HAVING >= 2 6 groups -> 3 survive name genre count Mickey Pop 2 Mickey Classic 1 Minnie Pop 1 Minnie Classic 2 Daffy Pop 2 Daffy Classic 1 ✓ ✓ ✓ 3 user-genre pairs survive: Mickey-Pop, Minnie-Classic, Daffy-Pop. Example 1: watch the table shrink Read in execution order: FROM and JOIN, then GROUP BY, then HAVING. The table transforms clause by clause; rows the final clause drops are greyed and struck through, survivors carry a green check. Color key: grey strike = a dropped row, green check = a surviving row. Notes ↗ sql-reading-queries · Example 2: Finding Power Users Example 2: three CTE layers, read inside-out Example 2: three CTE layers, read inside-out Per-user metrics, then a percentile rank, then the top-half filter. WITH user_stats AS ( ... per-user COUNT, AVG ... ), ranked_users AS ( ... PERCENT_RANK() OVER (ORDER BY avg_rating DESC) ... ) SELECT name, listen_count, avg_rating FROM ranked_users JOIN Users WHERE rating_rank < 0.5 CTE 1: user_stats per-user metrics name count avg Mickey 3 4.2 Minnie 3 4.4 Daffy 3 3.9 rank CTE 2: ranked_users add percentile rank name avg rank Minnie 4.4 0.0 Mickey 4.2 0.5 Daffy 3.9 1.0 filter WHERE rank < 0.5 keep the top half name avg rank Minnie 4.4 0.0 Mickey 4.2 0.5 Daffy 3.9 1.0 ✓ Only Minnie clears the top half: rank 0.0 < 0.5, average 4.4. Example 2: three CTE layers, read inside-out Per-user metrics, then a percentile rank, then the top-half filter. The table transforms clause by clause; rows the final clause drops are greyed and struck through, survivors carry a green check. Color key: grey strike = a dropped row, green check = a surviving row. Notes ↗ M1 SQL Problem Solving: Writing Queries Most queries you write are one of five shapes: filter to a subset, rank within groups, accumulate over time, compare to a group, or flag outliers.
5 figures sql-writing-queries · Master These 5 Patterns The Funnel pattern: keep listeners, subtract sharers 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. The Funnel: who did A but not B Each stage keeps fewer rows. The shape narrows. Played 1,000 1,000 Made a playlist 450 450 dropped 550 Shared 87 87 dropped 363 SELECT user_id, name FROM users WHERE user_id IN (SELECT user_id FROM listens) AND user_id NOT IN (SELECT user_id FROM shares) Notes ↗ sql-writing-queries · Master These 5 Patterns The Ladder pattern: top 3 songs per genre via ROW_NUMBER 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. The Ladder: top N within each group Number the rows per group; climb from rank 1, keep the top rungs. 1 Levitating 980 2 Blinding Lights 900 3 Stay 870 4 Peaches 410 keep rn ≤ 3 SELECT * FROM ( SELECT song, genre, plays, ROW_NUMBER() OVER ( PARTITION BY genre ORDER BY plays DESC) AS rn FROM songs) WHERE rn <= 3
Notes ↗ sql-writing-queries · Master These 5 Patterns The Timeline pattern: running total with SUM OVER ORDER BY 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). The Timeline: a running total over time SUM over an ordered window. Add each day's hours; the line only climbs. cumulative hours 3 Mon +3 8 Tue +5 10 Wed +2 14 Thu +4 SELECT date, hours, SUM(hours) OVER ( ORDER BY date ) AS cumulative FROM daily_listening Notes ↗ sql-writing-queries · Master These 5 Patterns The Comparison pattern: plays minus the genre average 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). The Comparison: a row against its group average Subtract the group's AVG, computed over the partition, from each row. Dua Lipa +700 Doja Cat +100 Sia -800 genre avg SELECT artist_name, genre, plays, plays - AVG(plays) OVER ( PARTITION BY genre ) AS vs_avg FROM artist_stats
Notes ↗ sql-writing-queries · Master These 5 Patterns The Exception pattern: flag the listen spike with LAG The Exception pattern: 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. The Exception: find the outlier LAG the previous value, compute the jump, keep only the spikes. daily listens normal Mon 100 +20% Tue 120 +8% Wed 130 +138% spike Thu 310 WITH changes AS ( SELECT song_id, date, listens, LAG(listens) OVER ( PARTITION BY song_id ORDER BY date) AS prev FROM daily_listens) SELECT * FROM changes WHERE (listens-prev)*100/prev > 50
Notes ↗ end of lecture 2 ~47 min of figures
Presenting
→ / space next build step, then next figure
← previous figure
↓ / ↑ one step forward / reset this figure
a reveal every step at once
g grid of every figure in this deck, with the lecture breaks
Home / End first / last figure
b break screen
Opening things
n the notes page for this figure
v the video, here, at this figure
s the schedule
q the nano quiz
y why this figure matters: the question it serves, and its level in the stack
c show the figure captions
Drawing on the slide
p / w pen colour / width
z / x undo one stroke / clear the slide
captions: the player's CC button
v or Esc to close