Module 6: SQL in a Real Replication

class: center, middle, inverse, title-slide

.title[
# Module 6: SQL in a Real Replication
]
.subtitle[
## Reading and reproducing one step of a real research pipeline
]

---

# Course Map

<a href="../module-01/slides.html#schema" style="font-size:13px;background:#f0f8ff;border:1px solid #aac;border-radius:4px;padding:2px 8px;text-decoration:none;color:#336;">Schema ref</a>

<table>
<tr><th>#</th><th>Module</th><th>Status</th><th>Sample Code</th></tr>
<tr><td>1</td><td><a href="../module-01/slides.html">SELECT, WHERE, Aggregates</a></td><td>✓ done</td><td><a href="../module-01/slides.html#sample">M1</a></td></tr>
<tr><td>2</td><td><a href="../module-02/slides.html">JOINs</a></td><td>✓ done</td><td><a href="../module-02/slides.html#sample">M2</a></td></tr>
<tr><td>3</td><td><a href="../module-03/slides.html">GROUP BY, HAVING, Conversion Funnels</a></td><td>✓ done</td><td><a href="../module-03/slides.html#sample">M3</a></td></tr>
<tr><td>4</td><td><a href="../module-04/slides.html">Subqueries and CTEs</a></td><td>✓ done</td><td><a href="../module-04/slides.html#sample">M4</a></td></tr>
<tr><td>5</td><td><a href="../module-05/slides.html">Window Functions</a></td><td>✓ done</td><td><a href="../module-05/slides.html#sample">M5</a></td></tr>
<tr><td><b>6</b></td><td><b>SQL in a Real Replication</b> <i>(you are here)</i></td><td>← current</td><td><a href="#sample">M6</a></td></tr>
</table>

---

# Why This Module Breaks the Pattern

Modules 1 to 5 use the ride-sharing database. This one does not, on purpose.

The skill is **reading and reproducing one step of a real pipeline**: the file
`01_rtb_ca.sql` in [`fhoces/opa-prop40`](https://github.com/fhoces/opa-prop40), which
turns daily Forbes billionaire snapshots into the California lists of a published paper.

The real snapshots are confidential, so the data here is a **synthetic panel in the same
shape**: 60 invented people, invented ids, random-walk fortunes.

The SQL is the **real file, nearly verbatim**. `module-06/solution.sql` differs only in its
banner and in four invented ids. Part 2 adds two more real files from the paper's second step.

---

# Two Steps From Raw Data to a Paper

**Step 1. Raw data to intermediate tables.** Daily snapshots (one row per person per day)
are reduced to the California year-end lists, an industry table and a daily total. The
authors keep these in a spreadsheet.

**Step 2. Tables to results.** The spreadsheet feeds the paper's figures and estimates.

Reproducing step 2 needs only the spreadsheet. Reproducing step 1 means rewriting the
reduction **and checking, cell by cell, that your tables match theirs**.

This module is step 1 for one pipeline: **four blocks, each creating one table**.

| Block | Table | What it holds |
|---|---|---|
| Q1 | `rtb_ca_all` | every California billionaire-day |
| Q2 | `rtb_ca_eoy` | seven year-end lists, stacked |
| Q3 | `rtb_ca_2026_01_01_industry` | wealth by industry, plus a Total row |
| Q4 | `rtb_ca_aggregate` | count and wealth per date |

---
name: data-main

# The Data: One Row per Person per Day

`rtb_all_combined`: 60 people × 293 dates (2019-12-31 to 2026-03-30), worth in **$ million**, missing values stored as **NULL**.

```sql
SELECT date, forbes_id, state, industries,
       forbes_worth, forbes_public_worth, forbes_private_worth
FROM rtb_all_combined
WHERE date = '2026-01-01'
ORDER BY forbes_worth DESC
LIMIT 4;
```

Table: 4 records

|date       |forbes_id         |state      |industries | forbes_worth| forbes_public_worth| forbes_private_worth|
|:----------|:-----------------|:----------|:----------|------------:|-------------------:|--------------------:|
|2026-01-01 |xavi-juniper      |California |Technology |     344709.7|            230266.1|             114443.6|
|2026-01-01 |quill-silverbrook |California |Technology |     290506.1|            119979.0|             170527.1|
|2026-01-01 |calla-vantongeren |California |Technology |     266399.7|             74325.5|             192074.2|
|2026-01-01 |marlo-zanderfell  |California |Technology |     178812.1|             62584.2|             116227.9|

Plus `rtb_ca_cik (forbes_id, cik)` and `rtb_residency_overrides (forbes_id, rule, note)`.

<a href="#data-dgp" class="nav-btn">How the panel is built →</a>

---
name: q1-main

# Q1: Who Counts as a California Billionaire?

<a class="write-ref" href="../module-04/slides.html#sample">M4</a>
*R idiom it replaces: a chain of `filter()` calls with two id vectors typed into the script.*

```sql
DROP TABLE IF EXISTS rtb_ca_all;
CREATE TABLE rtb_ca_all AS
SELECT date, forbes_id, forbes_name, state, ...   -- the ten input columns
FROM rtb_all_combined
*WHERE forbes_worth >= 1000
* AND (
*       state = 'California'
*       OR forbes_id IN (SELECT forbes_id
*                        FROM rtb_residency_overrides
*                        WHERE rule = 'include')
*     )
* AND forbes_id NOT IN (SELECT forbes_id
*                       FROM rtb_residency_overrides
*                       WHERE rule = 'exclude');
```

One boolean expression per row: the order of the `AND` terms does not matter, **the
parentheses do** (`AND` binds before `OR`). The ids live in a table, so the query can be
public while the list stays private.

---

# Q1: NULL in Comparisons, and the NOT IN Trap

**A NULL in a comparison is unknown, and `WHERE` keeps only true.**

- `NULL >= 1000` is unknown, so a row with no worth is dropped.
- `NULL = 'California'` is unknown, so a row with no state survives only via the include list.
- Both match what R's `filter()` does with `NA`.

**The trap.** If the exclude subquery returns a single NULL id:

```sql
SELECT 5 NOT IN (SELECT 7 UNION SELECT NULL) AS not_in_with_null;  -- NULL, never true
```

`x NOT IN (...)` is false for listed ids and unknown for everyone else, so **the block
returns zero rows**. The real loader refuses an override row without an id. The NULL-safe
form is `NOT EXISTS (SELECT 1 FROM ... WHERE id = x)`.

```sql
SELECT COUNT(*) AS rows_kept, COUNT(DISTINCT forbes_id) AS people FROM rtb_ca_all;
```

Table: 1 records

| rows_kept| people|
|---------:|------:|
|      8826|     35|

---

# Tables, Not Result Sets

Every block has the same skeleton:

```sql
*DROP TABLE IF EXISTS rtb_ca_eoy;     -- idempotent: the file can run twice
*CREATE TABLE rtb_ca_eoy AS           -- store the result instead of printing it
SELECT ...;
```

- **`CREATE TABLE ... AS SELECT`** saves the result, so the next block can read it and R
  and Python read it back with the same `SELECT`. (R analogy: assigning a data frame,
  not printing it.)
- **`DROP TABLE IF EXISTS`** makes reruns safe. Without it the second run fails with
  "table already exists".
- **`CREATE INDEX idx_rtb_ca_all_date ON rtb_ca_all (date)`** speeds up the later blocks,
  which all filter or group by date.

**No dot-commands.** Modules 1 to 5 start with `.headers on` / `.mode column`. Those are
`sqlite3` shell commands, not SQL. Python runs the shared file with `executescript` and R
with `DBI::dbExecute`, statement by statement, and both stop with a syntax error at the first dot-command.

---
name: q2-main

# Q2: Seven Year-End Lists in One Long Table

<a class="write-ref" href="../module-02/slides.html#sample">M2</a>
*R idiom it replaces: seven `filter(date == d) |> arrange(desc(worth))` data frames, one per sheet.*

```sql
CREATE TABLE rtb_ca_eoy AS
SELECT ca.date, ca.forbes_id, ..., ca.forbes_private_worth,
       cik.cik AS sec_cik
FROM rtb_ca_all ca
LEFT JOIN rtb_ca_cik cik
  ON  cik.forbes_id = ca.forbes_id
* AND ca.date = '2026-01-01'
*WHERE ca.date IN (
*       '2019-12-31', '2020-12-31', '2021-12-31', '2022-12-31',
*       '2023-12-31', '2024-12-31', '2026-01-01'
*     )
ORDER BY ca.date, ca.forbes_worth DESC, ca.forbes_id;
```

- **One table with a `date` column**: each old sheet is a `WHERE date = '...'` slice, and a
  new year is one more value in the `IN` list.
- **2026-01-01 is the "2025" list**: the data jump from 2025-12-30 to 2026-01-01.
- **`forbes_id` breaks ties**, so the order is the same on every run.

---

# Q2: The Date Test Belongs in ON, Not WHERE

Only the 2026-01-01 list gets `sec_cik`. With the test in `ON`, earlier rows find no match
and the `LEFT JOIN` keeps them with `sec_cik` NULL.

.pull-left[
.small[

```sql
SELECT date, COUNT(*) AS n_rows,
       COUNT(sec_cik) AS n_with_cik
FROM rtb_ca_eoy
GROUP BY date;
```

Table: 7 records

|date       | n_rows| n_with_cik|
|:----------|------:|----------:|
|2019-12-31 |     27|          0|
|2020-12-31 |     27|          0|
|2021-12-31 |     30|          0|
|2022-12-31 |     30|          0|
|2023-12-31 |     30|          0|
|2024-12-31 |     34|          0|
|2026-01-01 |     33|         23|
]
]

.pull-right[
**Move the test to `WHERE` and six lists vanish.** `WHERE` filters after the join, so
every row not dated 2026-01-01 is dropped.

**In R:** `left_join()` then
`mutate(sec_cik = if_else(date == "2026-01-01", cik, NA))`, not `filter()`.

**A NULL `sec_cik` means two things**: "not looked up" on the six earlier lists, and "no
CIK" (a NULL crosswalk value or no crosswalk row) on 2026-01-01.
]

---
name: q3-main

# Q3: The Industry Table as a CTE Chain

<a class="write-ref" href="../module-04/slides.html#sample">M4</a>
*R idiom it replaces: `summarise()` by industry, an ungrouped `mutate()` for shares, a one-row total, `bind_rows()`.*

One CTE per R step:

| CTE | What it does | Course |
|---|---|---|
| `list_2026` | the 2026-01-01 rows of `rtb_ca_eoy` | M1 |
| `by_industry` | `GROUP BY industries`, `COUNT(*)`, `COALESCE(SUM(...), 0) / 1000.0` | M3, M1 |
| `with_share` | each industry's share: `forbes_worth / SUM(forbes_worth) OVER ()` | M5 |
| `total_row` | the same sums over the whole list, labelled `'Total'` | M3 |
| `stacked` | `UNION ALL` puts the Total row under the industry rows | beyond |

The Sports owner never has a public/private split, so `SUM(forbes_public_worth)` is NULL
for that group. **`COALESCE(..., 0)` returns 0**, as R's `sum(x, na.rm = TRUE)` does.

<a href="#q3-detail" class="nav-btn">Full block →</a>

---
name: q3-window

# Q3: A Grand Total With an Empty OVER, a Total Row With UNION ALL

```sql
with_share AS (
  SELECT industries, n_billionaires, forbes_public_worth, forbes_worth,
         forbes_public_worth / forbes_worth          AS fraction_public_worth,
*        forbes_worth / SUM(forbes_worth) OVER ()    AS fraction_forbes_worth
  FROM by_industry
),
...
stacked AS (
  SELECT * FROM with_share
* UNION ALL
  SELECT industries, ..., forbes_worth / forbes_worth AS fraction_forbes_worth
  FROM total_row
)
SELECT * FROM stacked
*ORDER BY (industries = 'Total'), fraction_forbes_worth DESC, industries;
```

.small[
- **Empty `OVER ()`**: the window is every row, so the sum is the grand total (module 5 used `PARTITION BY`).
- **`UNION ALL`** keeps every row; plain `UNION` would also deduplicate.
- **`(industries = 'Total')`** is 0 or 1, so Total sorts last. SQLite sorts a `UNION` only by its output columns, hence the `stacked` CTE.
- **No `NULLIF` needed**: Q1 guarantees every group has worth of at least $1 billion.
]

---

# Q3: The Result

Sports: `public_bn` is 0, not NULL, thanks to `COALESCE`. Total is last, with a share of 1.

.small[

```sql
SELECT industries, n_billionaires,
       ROUND(forbes_public_worth, 1) AS public_bn, ROUND(forbes_worth, 1) AS worth_bn,
       ROUND(fraction_public_worth, 3) AS frac_public,
       ROUND(fraction_forbes_worth, 3) AS frac_worth
FROM rtb_ca_2026_01_01_industry;
```

Table: 9 records

|industries            | n_billionaires| public_bn| worth_bn| frac_public| frac_worth|
|:---------------------|--------------:|---------:|--------:|-----------:|----------:|
|Technology            |             17|     572.1|   1270.2|       0.450|      0.781|
|Finance & Investments |              6|     131.5|    202.1|       0.651|      0.124|
|Healthcare            |              3|      64.1|    103.5|       0.620|      0.064|
|Sports                |              1|       0.0|     16.4|       0.000|      0.010|
|Media & Entertainment |              2|       8.8|     15.1|       0.581|      0.009|
|Real Estate           |              1|       7.4|     11.6|       0.637|      0.007|
|Diversified           |              1|       0.2|      4.3|       0.056|      0.003|
|Fashion & Retail      |              2|       1.7|      3.3|       0.510|      0.002|
|Total                 |             33|     785.8|   1626.4|       0.483|      1.000|
]

---
name: q4-main

# Q4: Daily Totals With a Conditional Sum

<a class="write-ref" href="../module-01/slides.html#sample">M1</a>
*R idiom it replaces: `summarise(top4 = sum(worth[id %in% top4_ids]))`, subsetting inside the sum.*

```sql
CREATE TABLE rtb_ca_aggregate AS
SELECT date,
  COUNT(*)                                        AS n_billionaires,
  COALESCE(SUM(forbes_worth), 0) / 1000.0         AS forbes_worth_total,
* COALESCE(SUM(CASE
*                WHEN forbes_id IN ('marlo-zanderfell', 'calla-vantongeren',
*                                   'xavi-juniper', 'quill-silverbrook')
*                THEN forbes_worth
*              END), 0) / 1000.0                  AS forbes_worth_top4,
  COALESCE(SUM(forbes_private_worth), 0) / 1000.0 AS forbes_private_worth
FROM rtb_ca_all
*WHERE date NOT IN ('2022-07-18', '2026-03-29', '2026-03-30')
GROUP BY date
ORDER BY date;
```

.small[
- **No `ELSE`** means `ELSE NULL`: everyone outside the four is NULL, and `SUM` skips them.
- **`NOT IN` with a typed list** cannot contain a NULL by accident, so the Q1 trap does not apply.
- `forbes_private_worth` comes last: the authors' code computes it, their sheet does not have it.
]

---

# NULL Semantics in One Table

For a column holding **5, NULL, 7**:

| Expression | Result | Over an all-NULL group |
|---|---|---|
| `SUM(x)` | 12 (skips NULL) | NULL |
| `COALESCE(SUM(x), 0)` | 12 | 0, like R's `sum(na.rm = TRUE)` |
| `COUNT(*)` | 3 (counts rows) | the number of rows |
| `COUNT(x)` | 2 (counts non-NULL values) | 0 |
| `AVG(x)` | 6 (12 / 2, not 12 / 3) | NULL |
| `x >= 1000` in `WHERE` | NULL rows dropped | all dropped |
| `x NOT IN (SELECT ...)` with a NULL in the list | never true | zero rows kept |

The file counts billionaires with `COUNT(*)`: Q1 already dropped NULL worths, so every row
is a billionaire. `COUNT(forbes_public_worth)` would count only people with a split on file.

---

# Checking Against the Authors' Sheet, and Vintage Gaps

The real checker **never compares files line by line**. It aligns both sides on a key
(`forbes_id` within each date, `industries`, or `date`), then reports rows only in ours,
rows only in theirs, the largest absolute difference per numeric column (pass below
`1e-6`), and text cells that differ. Counts must match exactly.

**What matched:** every target. Year-end lists, industry table and daily totals to about
1e-12; public year-end totals to their 2-decimal rounding; R vs Python exports to zero.

**What did not at first, and why** (neither was a logic error):

1. **One person** on the 2026 list: the authors' saved sheet included an id that their
   shared script's override list did not. Sheet and script were different versions.
2. **25 extra dates** in our daily table: reported, not failed, because only shared dates
   have to agree.

**Vintage gap** = a difference you can name (an id, a set of dates, a column). If you
cannot name it, it is unexplained. Never widen a tolerance to hide it.

---
class: center, middle, inverse

# Part 2: The Paper's Second Step

Two more real files, `02_data_sec_agg.sql` and `03_ftb_b4a.sql`, run unchanged from R and Python

---
name: part2-main

# Part 2: Spreadsheet to Tables, Through Shared SQL

The real project reproduces step 2 from the **public** workbook. Two sheets are loaded
into SQLite and reduced by shared SQL files that R and Python both run. Before the SQL
they were R code.

| Block | Table | From | What it holds |
|---|---|---|---|
| Q5 | `data_sec_all_kept` | `data_sec_all` | the rows that count: no excluded id, no re-pasted copy |
| Q6 | `data_sec_agg` | Q5 | per year: `n` and 27 sums in $ billion |
| Q7 | `ftb_b4a_year` | `ftb_b4a` | per tax year: brackets and four totals |
| Q8 | `ftb_b4a_top` | `ftb_b4a` | the top-bracket rows, picked by label |

**`row_num`** is the row's position in the sheet. Q5 needs it to know which copy came
first. Q8 is about **not** using it to choose rows.

<a href="#part2-dgp" class="nav-btn">How the tables are built →</a>

---
name: q5-main

# Q5: Keep the First Copy With ROW_NUMBER

<a class="write-ref" href="../module-05/slides.html#sample">M5</a>
*R idiom it replaces: `filter(!forbes_id %in% exclude)` then `filter(!duplicated(df[c("year", "forbes_id", "forbes_worth")]))`.*

```sql
CREATE TABLE data_sec_all_kept AS
WITH kept AS (
  SELECT *
  FROM data_sec_all
* WHERE forbes_id NOT IN (SELECT forbes_id FROM data_sec_agg_exclude)
),
numbered AS (
  SELECT
    kept.*,
*   ROW_NUMBER() OVER (
*     PARTITION BY year, forbes_id, forbes_worth
*     ORDER BY row_num
*   ) AS copy_num
  FROM kept
)
SELECT *
FROM numbered
*WHERE copy_num = 1;
```

.small[
Each group of rows with the same year, id and worth is numbered 1, 2, 3 in sheet order; `copy_num = 1` keeps the first, which is `!duplicated()`.
]

---

# Q5: What Counts as a Copy

The tail of the synthetic sheet: rows 228 to 231 are pasted again as 232 to 235. Two of the four pairs:

.pull-left[
.small[

```sql
SELECT row_num AS r, forbes_id, forbes_worth AS worth,
       dividend,
       ROW_NUMBER() OVER (
         PARTITION BY year, forbes_id, forbes_worth
         ORDER BY row_num) AS copy
FROM data_sec_all
WHERE row_num >= 228
  AND forbes_id IN ('rhea-vantongeren', 'tamsin-kettleby')
ORDER BY forbes_id, r;
```

Table: 4 records

|   r|forbes_id        |    worth| dividend| copy|
|---:|:----------------|--------:|--------:|----:|
| 231|rhea-vantongeren |       NA|   6.5242|    1|
| 235|rhea-vantongeren |       NA|   6.5242|    2|
| 229|tamsin-kettleby  | 2011.811|   7.6373|    1|
| 233|tamsin-kettleby  | 2011.811|   8.1373|    2|
]
]

.pull-right[
.small[
- **A copy with a re-typed `dividend`** (tamsin) still matches on the key, so it goes.
- **A row with no worth** (rhea): `PARTITION BY` puts the two NULLs in one group, as `duplicated()` does with two `NA`s. A self-join on `a.forbes_worth = b.forbes_worth` misses it: `NULL = NULL` is not true.
- **`SELECT DISTINCT *` removes nothing**: `row_num` differs on every row.
- **Same id and year, different worth** (a 2022 pair, 8,000 and 5,256): two tracked fortunes, both kept.
- **Filter order** does not matter: the key includes `forbes_id`, so an excluded id never shares a group.
]
]

---
name: q6-main

# Q6: Yearly Sums, With COALESCE on Every Column

<a class="write-ref" href="../module-03/slides.html#sample">M3</a>
*R idiom it replaces: `group_by(year) |> summarise(n = n(), across(cols, \(x) sum(x, na.rm = TRUE) / 1000))`.*

```sql
CREATE TABLE data_sec_agg AS
SELECT
  year,
* COUNT(*)                                          AS n,
  COALESCE(SUM(forbes_worth), 0) / 1000.0           AS forbes_worth,
  COALESCE(SUM(forbes_public_worth), 0) / 1000.0    AS forbes_public_worth,
* COALESCE(SUM(option_profit), 0) / 1000.0          AS option_profit,
  ...                                               -- 27 sums in all
FROM data_sec_all_kept
GROUP BY year
ORDER BY year;
```

.small[
- **`COUNT(*)`**, not `COUNT(forbes_worth)`: one kept 2025 row has no worth, and `COUNT(col)` skips NULLs (29 instead of 30).
- **`COALESCE`**: `option_profit` is NULL for every 2019 row, so the plain `SUM` is NULL; R's `sum(na.rm = TRUE)` is 0.
- **`1000.0`**: integer by integer is integer division in SQLite (`7 / 2` is 3). The point zero keeps it floating point whatever the column type.
]

<a href="#q6-detail" class="nav-btn">Full block →</a>

---
name: q8-main

# Q7 and Q8: Pick Rows by Label, Not Position

<a class="write-ref" href="../module-04/slides.html#sample">M4</a>
*R idiom it replaces: `sum(ca_agi[242:300], na.rm = TRUE)` and "the top bracket is row 300".*

```sql
CREATE TABLE ftb_b4a_top AS
WITH labelled AS (
  SELECT row_num, taxable_year,
*        REPLACE(agic, '  ', ' ') AS agic,      -- '5,000,000  and  over'
         all_returns, ca_agi, taxable_income, total_tax
  FROM ftb_b4a
* WHERE taxable_year IS NOT NULL
)
SELECT taxable_year,
*      CASE agic
*        WHEN '5,000,000 and over'     THEN '5m_plus'
*        WHEN '5,000,000 to 9,999,999' THEN '5m_to_10m'
*        WHEN '10,000,000 and over'    THEN '10m_plus'
*      END AS bracket,
       row_num, all_returns, ca_agi, taxable_income, total_tax
FROM labelled
*WHERE agic IN ('5,000,000 and over', '5,000,000 to 9,999,999', '10,000,000 and over')
ORDER BY taxable_year, row_num;
```

.small[
Q7 is the same table through `GROUP BY taxable_year`, after `WHERE taxable_year IS NOT NULL` drops a footnote row with no year.
]

---

# Q8: Why Labels Survive a New Year and Positions Do Not

.pull-left[
.small[

```sql
SELECT taxable_year AS yr, bracket, row_num,
       all_returns AS n_returns
FROM ftb_b4a_top;
```

Table: 9 records

|   yr|bracket   | row_num| n_returns|
|----:|:---------|-------:|---------:|
| 2016|5m_plus   |      61|      9511|
| 2017|5m_plus   |      53|      8846|
| 2018|5m_plus   |      45|      9338|
| 2019|5m_plus   |      37|     10265|
| 2020|5m_plus   |      29|      9137|
| 2021|5m_to_10m |      20|      9298|
| 2021|10m_plus  |      21|      5579|
| 2022|5m_to_10m |      11|     10685|
| 2022|10m_plus  |      12|      6411|
]
]

.pull-right[
.small[
- **Blocks differ in length**: 8 brackets up to 2020, 9 from 2021, when the top bracket was split. Rows 11 and 12 hold 2022's top brackets; next year they hold 2023's.
- **The grader re-runs your file** on a copy with a 2023 block added at the top. A query that lists row numbers matches the first sheet and fails the second.
- **`REPLACE` first**: labels have two spaces around "to" and "and", but one 2019 label has one. `REPLACE` handles pairs left to right, so three spaces would become two; this sheet never has three.
- **Simple `CASE`**: compare one expression with a list of values; an unlisted label gives NULL, and the `WHERE` with the same list keeps those out.
- **`row_num` stays in the output** to sort and to trace each value to its sheet row, never to choose.
]
]

---

# The Drill

```bash
Rscript data/build_rtb_sample.R                       # once: database + expected/
python module-06/check.py module-06/exercise.sql      # blank: fails all 8 blocks
python module-06/check.py module-06/solution.sql      # reference: passes all 8
```

`check.py` copies the database to a scratch file, runs your file with `executescript`, and
prints one line per block:

```
Q1  PASS  rtb_ca_all (8826 rows)
Q2  FAIL  rtb_ca_eoy: 33 rows, expected 211
Q8  FAIL  ftb_b4a_top matches this sheet but not the same sheet with a 2023 block ...
```

**Break it on purpose**: move Q2's date test into `WHERE`; drop a `COALESCE` in Q3; drop
Q1's parentheses; forget Q4's skipped dates; use `SELECT DISTINCT` in Q5; count
`forbes_worth` in Q6; list row numbers in Q8. Each fails at its own block.

Then: the audio lesson (`module-06/lesson/`), the walking quiz (`module-06/quiz/`), and
the real files in `opa-prop40/bsz-analysis/sql/`.

---
class: inverse

# The Key Takeaways

### 1. Every block is `DROP TABLE IF EXISTS` + `CREATE TABLE AS`: rerunnable, and readable from R and Python alike.

### 2. One long table with a date column beats seven tables; a condition in `ON` joins some rows and keeps all.

### 3. Think about NULL first: `SUM` skips it, `COUNT(*)` does not, `NOT IN` breaks on it, `COALESCE` turns an empty sum into R's 0.

### 4. Choose rows by what they are (a key, a label), never by where they sit: `ROW_NUMBER` keeps first copies, `REPLACE` + `CASE` pick labels.

---
class: sample-code
name: sample

# Sample Code: The Daily Aggregate (Q4)

```sql
DROP TABLE IF EXISTS rtb_ca_aggregate;               -- rerunnable
CREATE TABLE rtb_ca_aggregate AS                     -- store, do not print
SELECT
  date,
  COUNT(*)                                        AS n_billionaires,
  COALESCE(SUM(forbes_worth), 0) / 1000.0         AS forbes_worth_total,
  COALESCE(SUM(CASE                                  -- conditional sum
                 WHEN forbes_id IN ('marlo-zanderfell', 'calla-vantongeren',
                                    'xavi-juniper', 'quill-silverbrook')
                 THEN forbes_worth                   -- no ELSE: NULL, skipped
               END), 0) / 1000.0                  AS forbes_worth_top4,
  COALESCE(SUM(forbes_private_worth), 0) / 1000.0 AS forbes_private_worth
FROM rtb_ca_all
WHERE date NOT IN ('2022-07-18', '2026-03-29', '2026-03-30')
GROUP BY date
ORDER BY date;
```

---

# Course Map

<a href="../module-01/slides.html#schema" style="font-size:13px;background:#f0f8ff;border:1px solid #aac;border-radius:4px;padding:2px 8px;text-decoration:none;color:#336;">Schema ref</a>

<table>
<tr><th>#</th><th>Module</th><th>Status</th><th>Sample Code</th></tr>
<tr><td>1</td><td><a href="../module-01/slides.html">SELECT, WHERE, Aggregates</a></td><td>✓ done</td><td><a href="../module-01/slides.html#sample">M1</a></td></tr>
<tr><td>2</td><td><a href="../module-02/slides.html">JOINs</a></td><td>✓ done</td><td><a href="../module-02/slides.html#sample">M2</a></td></tr>
<tr><td>3</td><td><a href="../module-03/slides.html">GROUP BY, HAVING, Conversion Funnels</a></td><td>✓ done</td><td><a href="../module-03/slides.html#sample">M3</a></td></tr>
<tr><td>4</td><td><a href="../module-04/slides.html">Subqueries and CTEs</a></td><td>✓ done</td><td><a href="../module-04/slides.html#sample">M4</a></td></tr>
<tr><td>5</td><td><a href="../module-05/slides.html">Window Functions</a></td><td>✓ done</td><td><a href="../module-05/slides.html#sample">M5</a></td></tr>
<tr><td>6</td><td>SQL in a Real Replication <i>(just finished)</i></td><td>✓ done</td><td><a href="#sample">M6</a></td></tr>
</table>

**You're done with the SQL course.** Drill the eight blocks until you can write each one cold, then read the real files.

---
class: center, middle, inverse

# Backup Slides

---
name: data-dgp

# Backup: How the Synthetic Panel Is Built

`data/build_rtb_sample.R`, seed 2026. Every person, id and number is invented.

.small[
- **Dates**: every 8th day from 2019-12-31 to 2026-03-30, plus the seven year-end dates and the three skipped dates. No 2025-12-31, as in the real data.
- **Worth**: a random walk in log worth per person (drift 0.003, sd 0.04 per step), starting log-uniform between $0.6 billion and $40 billion; the top 4 start at $90 to $160 billion. Some people cross the $1 billion line.
- **Hand-placed cases**, so every rule bites: one person whose state is Nevada but is included by the override table; one with no state, also included; one Californian excluded; two with no state and no override; two non-US citizens; one Sports owner with no public/private split ever.
- **Missing values**: about 3% of rows lose the public/private split, 0.5% lose worth.
- **Entry and exit**: ten people enter late, two drop out early.
- **CIK crosswalk**: one row per possible Californian, a quarter with NULL `cik`.
]

```sql
SELECT forbes_id, rule, note FROM rtb_residency_overrides;
```

Table: 3 records

|forbes_id       |rule    |note                                                           |
|:---------------|:-------|:--------------------------------------------------------------|
|wren-underhill  |include |course example: state on file is Nevada, counted as California |
|soren-driftwood |include |course example: no state on file, counted as California        |
|rhea-dunmore    |exclude |course example: state on file is California, never counted     |

---
name: q3-detail

# Backup: Q3, the Full Block (1 of 2)

```sql
DROP TABLE IF EXISTS rtb_ca_2026_01_01_industry;
CREATE TABLE rtb_ca_2026_01_01_industry AS
WITH list_2026 AS (
  SELECT industries, forbes_worth, forbes_public_worth
  FROM rtb_ca_eoy
  WHERE date = '2026-01-01'
),
by_industry AS (
  SELECT
    industries,
    COUNT(*)                                       AS n_billionaires,
    COALESCE(SUM(forbes_public_worth), 0) / 1000.0 AS forbes_public_worth,
    COALESCE(SUM(forbes_worth), 0) / 1000.0        AS forbes_worth
  FROM list_2026
  GROUP BY industries
),
with_share AS (
  SELECT
    industries, n_billionaires, forbes_public_worth, forbes_worth,
    forbes_public_worth / forbes_worth          AS fraction_public_worth,
    forbes_worth / SUM(forbes_worth) OVER ()    AS fraction_forbes_worth
  FROM by_industry
),
```

---

# Backup: Q3, the Full Block (2 of 2)

```sql
total_row AS (
  SELECT
    'Total'                                        AS industries,
    COUNT(*)                                       AS n_billionaires,
    COALESCE(SUM(forbes_public_worth), 0) / 1000.0 AS forbes_public_worth,
    COALESCE(SUM(forbes_worth), 0) / 1000.0        AS forbes_worth
  FROM list_2026
),
stacked AS (
  SELECT * FROM with_share
  UNION ALL
  SELECT
    industries, n_billionaires, forbes_public_worth, forbes_worth,
    forbes_public_worth / forbes_worth          AS fraction_public_worth,
    forbes_worth / forbes_worth                 AS fraction_forbes_worth
  FROM total_row
)
SELECT *
FROM stacked
ORDER BY (industries = 'Total'), fraction_forbes_worth DESC, industries;
```

---
name: part2-dgp

# Backup: How the Part 2 Tables Are Built

`data/build_rtb_sample.R`, second seed 4026 (the part 1 tables are unchanged by it).

.small[
- **`data_sec_all`**: the 38 possible Californians of the panel, each present in a year with probability 0.85 (the top 4 always), 2019 to 2025, sorted by year and worth. Worth grows about 6% a year with noise; the income and tax columns are random shares of worth, about 15% NULL.
- **Exclusion**: one invented id in `data_sec_agg_exclude` (in the real file, a person counted as a Californian for only part of the panel).
- **Two fortunes, one person**: in 2022 one id has rows worth 8,000 and 5,256. Both stay.
- **All NULL**: `option_profit` is empty for every 2019 row.
- **The re-paste**: the last four 2025 rows are appended again. One copy has a re-typed `dividend`; one row has no worth.
- **`ftb_b4a`**: taxable years 2022 down to 2016, newest first, sheet rows from 4. Eight brackets a year, nine from 2021. Labels with double spaces, one 2019 label with a single space, one suppressed (NULL) cell, and a footnote row with no year at the end.
]

---
name: q6-detail

# Backup: Q6, the Full Block

.pull-left[
.small[
```sql
DROP TABLE IF EXISTS data_sec_agg;
CREATE TABLE data_sec_agg AS
SELECT
  year,
  COUNT(*)                                   AS n,
  COALESCE(SUM(forbes_worth), 0) / 1000.0    AS forbes_worth,
  COALESCE(SUM(forbes_public_worth), 0)
    / 1000.0                                 AS forbes_public_worth,
  COALESCE(SUM(purchase), 0) / 1000.0        AS purchase,
  COALESCE(SUM(sale), 0) / 1000.0            AS sale,
  COALESCE(SUM(kg), 0) / 1000.0              AS kg,
  ...                     -- kg_long to w_pi, one line each
  COALESCE(SUM(total_tax), 0) / 1000.0       AS total_tax,
  COALESCE(SUM(economic_income), 0) / 1000.0 AS economic_income
FROM data_sec_all_kept
GROUP BY year
ORDER BY year;
```
]
]

.pull-right[
.small[

```sql
SELECT year, n,
       ROUND(forbes_worth, 1) AS worth_bn,
       option_profit AS opt_bn
FROM data_sec_agg;
```

Table: 7 records

|year |  n| worth_bn|    opt_bn|
|:----|--:|--------:|---------:|
|2019 | 35|    641.4| 0.0000000|
|2020 | 33|    723.0| 1.2805693|
|2021 | 32|    719.6| 0.7682987|
|2022 | 35|    762.0| 0.4440023|
|2023 | 30|    719.1| 0.2513092|
|2024 | 31|    808.8| 1.4260146|
|2025 | 30|    948.0| 1.4810304|
]

2019's `option_profit` is 0, not NULL: every 2019 value is NULL, and `COALESCE` turns the empty sum into R's 0.
]