Chapter 5
Your exercise
Our “hard” questions had lots of joins, but they were clearly worded and the data was clean. Modern models write long SQL easily. What trips them up is the kind of thing that trips up a new analyst in their first week.
Five kinds of difficulty that work
| Kind | Pattern | Why it’s hard |
|---|---|---|
| 1. Data quirks | Duplicate names, missing values, several values in one field | The SQL runs but counts the wrong thing. The model only sees 3 sample rows |
| 2. Business definitions | “Active customer”, “churned”, “repeat buyer” | The model must choose a definition, and a different choice gives a different answer |
| 3. Never / every | “Customers who never bought X” | Needs the opposite of a join, which is often subtly wrong |
| 4. Best within each group | “Each country’s best-selling genre” | Easy to return one overall winner instead of one per group |
| 5. Two sources that should agree | The invoice total vs the sum of its lines | The model must pick the right one, or notice they differ |
Three quirks in this database to explore
- There are 18 playlists, but some names appear twice (look for “Music” and “Movies”).
- 977 of the 3,503 tracks have no composer, and the composer field often holds several people in one string, like
A, B / C. - Customers have a
Countryand invoices have aBillingCountry. Are they always the same?
In Terminal, from the fireworks-learning folder:
sqlite3 data/chinook.db "SELECT PlaylistId, Name FROM Playlist ORDER BY Name;"sqlite3 data/chinook.db "SELECT Composer FROM Track WHERE Composer LIKE '%/%' LIMIT 10;"The task
Write 3 questions in plain English. No SQL yet.
- One that walks into a data quirk (kind 1 or 5)
- One that uses “never” or “each …’s top” (kind 3 or 4)
- One with a business definition the model has to choose (kind 2)
A good test question has one clearly right answer once you know the data, but is easy to get subtly wrong. If even you can’t say what the right answer is, it’s too vague to mark.
Send the three to Claude. Next we’ll check which ones will genuinely split the models, then you’ll write the gold SQL for each.