SQL for Data Science · Advanced
Now make it survive real data
The beginner track gets you to a number you can defend on one table. This one is what happens when the table is bigger than your intuition: the grain is wrong, the shape is wrong, the period is half over, and somebody wants to know whether a change actually worked. Every chapter is a decision, not a function.
Expect subqueries, CTEs and window functions throughout — this half assumes all of the free SQL tutorial and the beginner track. Start there if any of that is new; nothing here is a prerequisite for getting hired. Both halves are included with Basic.
Sign in to track your progress across chapters.
Phase 1 Dirty data
6 chaptersThe table is wrong before you touch it. Which number to trust, which duplicate to keep, and what you are allowed to throw away.
- 01The aggregate that lies9 minEight rides, one question, four different answers — all of them produced by correct SQL. Learning which one you actually meant.
- 02Which duplicate survives11 minDeduplication is not a technique, it is a choice about who to keep — and every choice breaks a different downstream number.
- 03One city, four spellings10 minFree-text columns are a list of everything anyone ever typed. Enumerate it first, normalise what is mechanical, and put the rest somewhere a human can review.
- 04Pick the grain before you query11 minOne row per what? Declare the answer before you write the join, then prove the join kept it. Almost every inflated total is a grain that changed and nobody noticed.
- 05What you exclude, and why12 minCancelled orders, test accounts, one enormous outlier. Every number you report is a number after exclusions — the job is choosing them deliberately and writing them down where the number lives.
- 06The check you run before you trust a table12 minSix questions, always the same six, run before you know what the analysis is. Row count, null rate, key, duplicates, orphans, cardinality — and what each answer should make you do next.
Phase 2 Reshape it
4 chaptersTurning rows you were given into the table the question actually needs — the shape, the buckets, the columns nobody wrote down.
- 07Long or wide11 minPivoting is easy; choosing the shape is the decision. Long survives a new category, wide fits on a page, and the pivot invents every cell that was never in the data.
- 08Where you put the bucket boundaries12 minBands are not in the data — you invent them. Half-open boundaries, who owns the value sitting on the line, fixed bands versus quantiles, and why a bucket edit re-cuts every number you ever charted.
- 09Deriving the column that isn't there12 minFlags and categories you invent with CASE are assertions, not facts. Where the unknown row goes, where the threshold lives, and how to name a derived column so somebody can check it.
- 10One row per entity13 minCollapsing an event log into the analysis table a model or a dashboard consumes — without dropping the entities that did nothing, and without mixing columns from different rows.
Phase 3 Change over time
4 chaptersPeriods that lie, windows that smooth away the story, cohorts, streaks, and the population that quietly selected itself.
- 11Period over period without lying12 minMonth-over-month is one subtraction and four ways to be wrong: a month with no rows, a month that is not over, a base of zero, and a base too small to divide by.
- 12Rolling windows and smoothing13 minA seven-day average is three different numbers depending on what you meant by seven days — and the window size you pick is an editorial decision about what the reader is allowed to notice.
- 13Cohorts and retention curves13 minNothing in the data says who is in a cohort, when month zero starts, or what counts as retained. Three definitions, and every one of them moves the curve.
- 14Gaps, streaks, and who survived13 minThe rows that are not there carry the finding. Build the calendar to see them, decide what consecutive means, and check who dropped out of the population before you average anything.
Phase 4 Measure and test
5 chaptersSomebody shipped a change and wants to know if it worked. Reading the result, checking it was a fair test, and the four ways it fools you.
- 15Reading an experiment readout12 minSomebody shipped a change and sent you a table. Work out what its rows actually are, which of the four true numbers you were handed, and how little it takes to move any of them.
- 16Was the split even?13 minBefore you read a result, check the experiment happened. Duplicate rows, users in both arms, and an arm that is quietly missing a fifth of its people all produce a readout that looks completely normal.
- 17Distributions before means12 minAn average is a one-number summary of a shape you have not looked at. One user out of forty here moves it by 167% and reverses the result — so look at the shape first, then choose which summary you are willing to defend.
- 18Significance without a stats library13 minA pooled standard error, a z statistic and a seeded table of critical values are enough to say whether a difference is worth believing — and the same three numbers tell you how many users you needed before you started.
- 19The four ways a readout fools you14 minEven split, correct denominator, adequate sample, significant result — and the answer is still wrong. Peeking, novelty, a mix that reverses on pooling, and the metric nobody put in the readout.
Included with Basic
The first section of every chapter is open — read one before you decide. Basic opens the rest of this track, the full question bank, and Data Lab, where the same work happens on a warehouse too big to eyeball.
See Basic plans