Day 37 · Matching guest lists

SQL II — Joins & Modeling

You will be able to

Write INNER and LEFT joins and predict their row counts before running them
Model a many-to-many relationship with a junction table
Use foreign keys to keep references honest
Break a nested query into readable CTEs (WITH clauses)
Apply a window function to answer a per-group ranking question

Today's ~120 minutes

Spaced-rep warm-up: due cards (SQL I pipeline, NULL rules)	10 min
ELI5 + tech read; sketch the join visualizer's matching by hand	18 min
Guided: four joins + junction table + window taste	42 min
Practice: questions only joins can answer	20 min
Project: task-tracker schema design, commit	20 min
Quiz + flashcards	10 min

Builds on: Day 36 — SQL I — tables & queries · Day 9 — OOP I — modeling entities

The analogy

🧸 ELI5⚙ Tech

You are running a wedding with two clipboards: the GUEST list (name, table number) and the RSVP list (name, meal choice). "Who is coming and what do they eat?" means walking both lists and MATCHING rows by name — that is a join, and the join condition ("same name") is the matching rule.

The interesting question is what to do with mismatches. Aunt Marta RSVP'd but is not on the guest list; cousin Theo is invited but never replied. An INNER join keeps only clean matches — Marta and Theo both vanish, silently. A LEFT join keeps EVERYONE on the left clipboard, padding missing right-side info with blanks (NULLs) — Theo appears with an empty meal box, which is exactly how you FIND the non-responders: look for the blanks. Choosing the join type is choosing which mismatches you care about.

Why two clipboards at all? Because one giant list repeats itself into chaos: write each guest's table number on every line they appear and someone WILL update one copy and not the other — now Theo sits at two tables. Splitting facts into separate lists so each fact lives exactly once, then joining on demand, is the whole idea of relational modeling.

Why this matters on the job

Real schemas are always multiple tables, so real queries are always joins — the SQL interview screen is essentially a joins-and-groups exam, with "find rows that DIDN'T match" (the LEFT JOIN / IS NULL move) as its favorite trick. Modeling is the deeper FDE skill: on-site, your first hour with a customer's data is spent reading their schema like a map — junction tables reveal the many-to-many relationships, foreign keys reveal what references what. You will design schemas yourself for the Day 42 service and the capstone's document/chunk/citation store (Day 119), and a bad early model taxes every feature after it.

Watch it happen

Matching guest lists — an inner join, row by row

step 1 / 6

	id	name
u1	1	Ana
u2	2	Ben
u3	3	Dana

Table one: users. Each row has a PRIMARY KEY (id) — a unique handle other tables can point at. Ana is 1, Ben is 2, Dana is 3.

🎯 Predict modespeed1×

Guided practice

guided 1

Two clipboards, four joins

20 min

Create joins_lab.py with the starter: customers and orders tables, foreign keys ON, seeded so that one customer has NO orders and several customers have many.
Before running the INNER join, predict its row count from the seed data (count the matches by eye). Run and check. Do the same for the LEFT join — whose meal box is empty?
Run the anti-join (LEFT JOIN … IS NULL) to find the customer with no orders. Say the idiom out loud — it is the single most-asked SQL interview move.
Demonstrate the multiplication hazard: SUM(orders.total) joined against customers is fine, but join customers to orders AND order_items (starter includes a mini version) and watch a naive SUM double-count. Fix it by aggregating orders in a CTE first.
Turn foreign keys OFF (comment the PRAGMA), insert an order for customer 999, turn them back on and try again. The refusal is the point.

🐍 python — editable, runs in your browser

import sqlite3

conn = sqlite3.connect(":memory:")     # throwaway db, lives in RAM
conn.execute("PRAGMA foreign_keys = ON")
conn.executescript("""
    CREATE TABLE customers (
        id   INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        city TEXT NOT NULL
    );
    CREATE TABLE orders (
        id          INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customers(id),
        total       REAL NOT NULL CHECK (total >= 0),
        placed_on   TEXT NOT NULL
    );
    INSERT INTO customers (id, name, city) VALUES
        (1, 'Amara', 'Lagos'), (2, 'Bo', 'Seoul'),
        (3, 'Chen', 'Taipei'), (4, 'Dee', 'Austin');   -- Dee: no orders
    INSERT INTO orders (customer_id, total, placed_on) VALUES
        (1, 120.0, '2026-07-01'), (1, 35.5, '2026-07-11'),
        (2, 89.9,  '2026-07-03'), (3, 240.0, '2026-07-08'),
        (3, 15.0,  '2026-07-15'), (3, 60.0,  '2026-08-01');
""")

def q(sql):
    print("\n--", sql.strip().splitlines()[0])
    for row in conn.execute(sql):
        print("  ", row)

q("""
    SELECT c.name, o.total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id      -- INNER: matches only (6 rows)
""")

q("""
    SELECT c.name, o.total
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id -- everyone; Dee gets NULL (7 rows)
""")

q("""
    SELECT c.name                              -- the anti-join idiom
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    WHERE o.id IS NULL
""")

q("""
    WITH spend AS (                            -- aggregate BEFORE joining
        SELECT customer_id, SUM(total) AS lifetime, COUNT(*) AS n
        FROM orders GROUP BY customer_id
    )
    SELECT c.name, COALESCE(s.lifetime, 0) AS lifetime, COALESCE(s.n, 0) AS orders
    FROM customers c LEFT JOIN spend s ON s.customer_id = c.id
    ORDER BY lifetime DESC
""")

try:
    conn.execute("INSERT INTO orders (customer_id, total, placed_on) VALUES (999, 1, '2026-01-01')")
except sqlite3.IntegrityError as e:
    print("\nforeign key said no:", e)

Ctrl/⌘+Enter runs · Tab indents · numpy/pandas/sklearn auto-load on import (torch and network calls need a local run)

guided 2

Many-to-many + a window function taste

22 min

Extend the lab: create tags and order_tags (junction) tables per the starter — an order can be "gift", "rush", AND "fragile"; a tag applies to many orders. Note the junction table's composite primary key: the relationship itself is the row, and duplicates are refused.
Query through the junction: all orders tagged "rush", then tag frequencies (tags LEFT JOIN order_tags GROUP BY tag — so unused tags show 0, not vanish).
Try to express "each customer's LARGEST order, with its date" using plain GROUP BY. Feel the wall: MAX(total) collapses the rows and you cannot recover WHICH row won (selecting placed_on alongside MAX is not guaranteed to match in general SQL). This wall is what windows break.
Now the window version from the starter: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) ranks orders per customer without collapsing; the CTE filters rank 1. Read the result row by row and say what PARTITION BY and ORDER BY each did.
Change it to top-2 per customer with one character. That flexibility is why analysts live inside window functions.

🐍 python — editable, runs in your browser

conn.executescript("""
    CREATE TABLE tags (
        id   INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE
    );
    CREATE TABLE order_tags (
        order_id INTEGER NOT NULL REFERENCES orders(id),
        tag_id   INTEGER NOT NULL REFERENCES tags(id),
        PRIMARY KEY (order_id, tag_id)          -- the relationship IS the row
    );
    INSERT INTO tags (id, name) VALUES (1, 'gift'), (2, 'rush'), (3, 'fragile'), (4, 'bulk');
    INSERT INTO order_tags VALUES (1, 1), (1, 2), (3, 2), (4, 3), (5, 1), (5, 2), (5, 3);
""")

q("""
    SELECT o.id, c.name, o.total                -- orders tagged 'rush'
    FROM orders o
    JOIN order_tags ot ON ot.order_id = o.id
    JOIN tags t       ON t.id = ot.tag_id
    JOIN customers c  ON c.id = o.customer_id
    WHERE t.name = 'rush'
""")

q("""
    SELECT t.name, COUNT(ot.order_id) AS uses   -- unused tags show 0
    FROM tags t
    LEFT JOIN order_tags ot ON ot.tag_id = t.id
    GROUP BY t.name ORDER BY uses DESC
""")

q("""
    WITH ranked AS (
        SELECT customer_id, total, placed_on,
               ROW_NUMBER() OVER (
                   PARTITION BY customer_id     -- restart ranking per customer
                   ORDER BY total DESC          -- rank by size within each
               ) AS rk
        FROM orders
    )
    SELECT c.name, r.total, r.placed_on
    FROM ranked r JOIN customers c ON c.id = r.customer_id
    WHERE r.rk = 1                              -- change to <= 2 for top-2
    ORDER BY r.total DESC
""")

Ctrl/⌘+Enter runs · Tab indents · numpy/pandas/sklearn auto-load on import (torch and network calls need a local run)

On your own

Questions only joins can answer

20 min

Against the full lab schema, one query each: (1) each city's total revenue, including cities whose customers never ordered (must show 0); (2) customers whose EVERY order is tagged (hint: compare counts); (3) the most recent order per customer with its date — window function; (4) pairs of tags that co-occur on the same order (self-join the junction table; avoid duplicate pairs with tag_id < tag_id); (5) each customer's share of total revenue as a percentage (CTE for the grand total, then join or a window SUM() OVER ()).

Constraints: no Python-side computation; every answer is one statement. Check (1) and (5) by hand against the seed data.

Hints: (2) count orders vs count distinct tagged orders per customer; (4) the pair table is order_tags joined to itself on order_id.

Ship before you stop

Schema design: the task tracker, relationally

Redesign your Day 7/9 task tracker as a relational schema in tracker_schema.py: users, projects, tasks (belonging to a project, assigned to a user, with status and due date), and labels with a task_labels junction — at minimum. Enforce the contract: foreign keys ON, CHECKs for status values, composite key on the junction. Seed realistic data (2 users, 2 projects, 8 tasks, overlapping labels), then answer five product questions with joins: open tasks per user, projects with no incomplete tasks, label frequencies, the most overdue task per project (window), and tasks sharing at least one label with a given task. Write one paragraph in the docstring: which facts live in exactly one place, and one duplication you deliberately avoided. Commit.

Rubric — check what you completed (0/5)

Schema has ≥ 4 tables including a junction with a composite primary keyForeign keys enforced and demonstrated with one rejected orphan insertAll five product questions answered in single statements; window function used for per-project overdueThe no-incomplete-tasks query correctly uses an anti-join or NOT EXISTSDocstring paragraph explains single-source-of-truth choices; committed

Common mistakes & misconceptions

Using INNER JOIN when the question includes zeros ("revenue per city, including cityless"). INNER silently drops non-matches; LEFT keeps them. Decide which mismatches matter BEFORE picking the join.
Filtering the right table of a LEFT JOIN in WHERE (WHERE o.total > 50) — that turns it back into an INNER join by discarding the NULL-padded rows. Right-side filters belong in the ON clause.
Summing across a one-to-many join and double-counting. Aggregate the many-side in a CTE first, then join once per entity.
Modeling many-to-many with a comma-separated column ("tags: gift,rush"). It defeats indexing, joining, and constraints — the junction table is the relational answer.
Forgetting PRAGMA foreign_keys = ON in SQLite — references are silently unenforced by default, and orphans creep in.
Selecting a non-aggregated column alongside MAX() and trusting they come from the same row. Use a window function (ROW_NUMBER … rk = 1) for "the row that won."

Knowledge check

Q1. Customers: 5 rows, one with no orders. Orders: 9 rows. How many rows do INNER and LEFT join (customers to orders) return?

Q2. Which schema correctly models posts that have many tags and tags used by many posts?

Q3. "Show each department's three highest-paid employees." What makes this a window-function problem?

Go deeper — curated resources

courseSQLBolt — lessons on JOINs (6–8) and review ↗25 min docsSQLite Documentation — SELECT & window functions ↗20 min docssqlite3 — official Python docs (placeholders, executescript) ↗10 min

If you have a third hour

Normalization, less informally — Look up 1NF/2NF/3NF with examples and translate each into the "every fact once" rule. Then read one argument FOR denormalization (read-heavy analytics) and note when you would break the rules knowingly.

Done means

Join row counts predicted correctly before running
Anti-join and CTE-before-join patterns used and explained
Junction table queried in both directions; window top-1 works
Tracker schema committed with five product queries green
Quiz ≥ 2/3

How this connects

← Back: Yesterday's single-table contract (Day 36) becomes a multi-table map today; the entity thinking mirrors Day 9's classes (a table is a dataclass the database enforces). The NULL rules from Day 36 are exactly what makes LEFT-join padding work.

Forward →: Day 38 explains what these joins COST and how indexes rescue them. The Day 42 service persists into a two-table version of today's modeling, Day 66's pandas merge is this API in DataFrame clothing, and the capstone's documents/chunks/citations schema (Day 119) is a straight reuse.

Unlocks: D38 Database Internals · D66 pandas II — Wrangling