Data, SQL and Getting to the Answer
Most AI problems turn out to be data problems. This is where you learn to ask a database a question and defend the answer.
- Level
- Nothing assumed
- Lessons
- 66
- Reading time
- 560 min
- Price
- Free, no sign-up to read
A first course in data for people who have never written a query. You will learn SELECT and WHERE, how to reason about joins instead of memorising diagrams, GROUP BY, the NULL rules that quietly break counts, what an index actually does, how to clean real exported data without destroying it, and how to tell a number that is technically correct from a number that answers the question you were asked. It ends with the part nobody teaches: how to get SQL out of an AI without being confidently misled.
Opens after the Building Apps With AI exam
Sign in, finish that course, and pass its exam. You can read this syllabus meanwhile.
Go to Building Apps With AIModule 1
Tables, types and the question you are actually asking
Before any clever query there is a table, and a table is a set of promises: one kind of thing per row, a declared type per column, a key that survives a name change. This block gets a real database onto the machine you already own, teaches the vocabulary a schema is written in, and ends with the single sentence that prevents most wrong answers in the rest of the course.
By the end you can
Set up a working database on your own machine, read an unfamiliar schema well enough to say what one row of each table means and which columns join to which, and state the grain of any result you are about to compute before you compute it
- 1When a spreadsheet stops workingA database stores each fact once and enforces rules; a spreadsheet stores copies and enforces nothing.
- 2Getting a database onto the machine you haveYou do not need a server to practise SQL: SQLite is one file, DuckDB queries a CSV where it lies, and Postgres is what to install when a job advert names it.
- 3What a column type actually buys youA column type is a refusal the database makes on your behalf; choose numeric for money, because a binary float cannot hold 0.10 exactly and the errors add up.
- 4Keys: how a database knows one thing from anotherA primary key answers "is this the same thing as before?", so it must be meaningless and permanent; a business rule such as a unique email sits beside it and is allowed to change.
- 5Where each fact should liveStore each fact once, where it is true; a value copied onto a row is only a duplicate if it is still supposed to change when the original does.
- 6SELECT and WHERE: asking for rowsWHERE tests one row at a time, in isolation; it never sees the other rows that share a customer.
- 7Sorting, limits, and the rows that move between runsWithout ORDER BY a result has no order, and with ORDER BY on a column that ties it has no order among the ties; add a unique column as the final sort key before you paginate.
- 8CASE, COALESCE and computing a column that does not existCASE tests its branches strictly top to bottom and returns the first match, so the order you write the conditions in is part of the logic, not a matter of style.
- 9Reading a database somebody else builtIn a schema you did not build, let the foreign keys draw the map, state each table's grain, and hunt for the status and soft-delete columns that silently filter everything.
- 10Grain: the sentence to say before you write the querySay "one row of my result is one ___" before writing the query; every aggregate reads the joined grain, not the original table's, so a join that changes the grain is the whole hazard.
- 11A right number and a useful number are not the sameA correct query answers the question you typed, which is rarely the question you were asked.
- 12Asking an AI for SQL without being lied toPaste your schema, ask for assumptions first, and check the number against something you already know.
Module 2
Putting tables together
Joins are where most SQL goes wrong, and almost always for one of four reasons: a key that does not match, a relationship that matches more than once, two child tables counted at the same time, or a filter placed on the wrong side. This block works through each mechanism, then adds the other ways of combining rows — set operations, subqueries, CTEs and a generated spine — so you can pick the one that keeps your grain intact.
By the end you can
Combine any set of related tables without inflating a total: diagnose a join key that silently fails to match, recognise and defuse the fan trap between two child tables, choose between a join, a subquery, an EXISTS and a set operation, and generate the rows for periods in which nothing happened
- 13Joins: reason about them, do not memorise themA join emits one row per match; zero matches and two matches are where every join bug lives.
- 14The four joins, and what each one is forAll four joins share one mechanism, one output row per match, and differ only in what they do with the leftovers; the row count after a join is the check that tells you whether a key matched more than once.
- 15When the join key looks right and matches nothingZero matched rows is usually a lie told by a type mismatch, an invisible character, a case or leading-zero difference, or a NULL; the diagnosis is to compare one pair of values by hand.
- 16Many-to-many, and the table in the middleA many-to-many relationship needs a table in the middle, and when you count across it a LEFT JOIN's unmatched row still counts as a row unless you count the child's column rather than `*`.
- 17Two child tables, one very wrong totalTwo one-to-many joins from the same parent multiply each other; pre-aggregate each child to one row per parent, then join the summaries.
- 18Joining a table to itselfA self-join is the same table under two names; add `a.id < b.id` so that no row matches itself and no pair appears twice.
- 19UNION, INTERSECT and EXCEPTUNION removes rows that are identical in every column, so it silently deletes genuine repeats; UNION ALL keeps everything and is the one to reach for by default.
- 20Subqueries: a query inside a queryReach for EXISTS and NOT EXISTS over IN and NOT IN: they say what you mean, cannot be poisoned by a NULL, and let the planner turn them into a join.
- 21CTEs: naming the stepsA CTE names a step so a query reads in order; it is not a saved result, and since Postgres 12 it is inlined into the main query unless you write MATERIALIZED.
- 22Generating the rows that are not thereGROUP BY cannot produce a row for a period in which nothing happened; generate the spine of expected rows, LEFT JOIN the facts to it, and count the fact column rather than `*`.
Module 3
Summaries, and the piles behind them
A summary is a claim about a pile of rows, and most wrong summaries are right about the arithmetic and wrong about the pile. This block starts with GROUP BY and NULL, then works through the places an aggregate quietly changes meaning: integer division, averages of averages, percentiles, week boundaries, rates with a moving denominator, subtotals, wide reports built from long data, and the reconciliation habit that catches all of them before a number leaves your hands.
By the end you can
Compute any count, sum, average, percentile or rate at a stated grain with NULLs and time boundaries handled deliberately, produce subtotals and a wide report from long data in one query, and reconcile every total against a figure you already trust before sending it
- 23GROUP BY: making piles and asking about each oneEach aggregate answers a question about one pile, and COUNT(*) and COUNT(DISTINCT) are different questions.
- 24NULL, and why your counts are wrongWHERE keeps only rows where the test is true, and every comparison with NULL is unknown.
- 25Averages: the four ways AVG misleadsAn average is a sum divided by a count, and both halves can be silently wrong: integers truncate, unequal rows need weights, and averages of averages answer a different question than the average of the rows.
- 26Percentiles: describing a distribution instead of its meanA percentile answers "what value does a given share of rows fall below?", and unlike sums, percentiles from two groups cannot be combined, so the raw rows or a sketch must be kept.
- 27Grouping by day, week and month without lying about the boundariesGroup time with half-open ranges, a named time zone and a stated week start, because BETWEEN drops the last day, engines disagree on Monday versus Sunday, and a month is a different length from the last one.
- 28Rates: a numerator, a denominator, and the same grain for bothCompute a rate's numerator and denominator in one query from one filtered pile, sum each before dividing, guard the division with NULLIF, and show the denominator beside the result.
- 29Listing the members of a pile: string_agg and array_aggstring_agg and array_agg keep a pile's members instead of counting them; order inside the aggregate, watch MySQL's silent 1,024-character cut, and never store the joined list back into a column.
- 30The row behind the MAX: which order was the biggestMAX returns a value with no memory of its row; to get the row, join back to the summary, use DISTINCT ON or arg_max, and compare output rows against group count to catch ties and all-NULL groups.
- 31Subtotals and grand totals in one query: ROLLUP and GROUPINGROLLUP stacks several grains in one result, so use GROUPING() to tell a subtotal's NULL from a real one and never sum across the output, which counts each row once per level.
- 32Wide and long: pivoting rows to columns and backStore and compute in long form and pivot only for display, because a query's columns are fixed at plan time and cannot be discovered from the data, and a wide table needs a schema change for every new period.
- 33Reconciling: making a total tie out before you send itBefore a total leaves your hands, make the grouped parts sum to the ungrouped whole, keep a row-count ledger across each join, tie the figure to an external number with the difference explained, and check one row by hand.
Module 4
Windows, sequences and time
GROUP BY collapses a pile into one row. A window function looks at the pile and leaves the rows where they are, which is what every "compared with the previous one", "rank within the group", "running total" and "seven-day average" question needs. This block builds the window from its three parts, partition, order and frame, works through the questions it answers, and finishes with the two time problems that defeat plain joins: what was true as of a date, and how a cohort of customers behaves month by month.
By the end you can
Answer any "compared with what came before" or "which one within each group" question — rank in group, top N per group, running total, change from the previous row, moving average, streaks, sessions and cohort retention — with a window function whose frame you can state, and join a fact to the value that was true at its own moment in time
- 34A window: GROUP BY that keeps the rowsA window function computes an aggregate over a row's partition and writes it beside the row instead of collapsing the rows, and because it runs after WHERE you filter on its result in a second layer.
- 35ROW_NUMBER, RANK and DENSE_RANK: three answers to "what position"row_number breaks every tie arbitrarily unless you give it a tiebreaker, rank shares positions and skips, dense_rank shares and does not skip, and the row count of a filtered ranking tells you which one you actually needed.
- 36Top N per group in two linesTop N per group is a ranking window in a CTE and a filter on the rank outside it; the function you rank with is a statement about ties, and the ORDER BY inside the window must end in a column that cannot tie.
- 37Running totals, and the frame you did not know you setAn ORDER BY inside a window sets a frame whose default, RANGE up to the current row, includes tied rows; write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly when you mean one row at a time.
- 38LAG and LEAD: the previous row, and the change since itLAG and LEAD read a fixed number of rows back or forward in the window's order, not a fixed distance in time, and last_value returns the current row unless the frame is extended to UNBOUNDED FOLLOWING.
- 39Moving averages: seven rows is not seven daysA ROWS frame counts rows, so a seven-row average over a table with missing days is not a seven-day average; join to a spine or use a RANGE frame measured in time, and mark the first partial windows.
- 40Streaks and sessions: gaps and islandsLabel each run of consecutive values with date minus row_number, or each session with a running sum of gap flags, then group by the label; the gap threshold is a definition that changes every downstream count.
- 41Cohorts and retention: how a month of new customers behaves afterwardsA cohort is a group defined by a first event, retention is the share of it active at each age, and the grid is a triangle you read down a column only as far as every cohort has reached.
- 42Dates, intervals and the two kinds of timestampStore timestamptz and convert with a named zone at display time, because a plain timestamp discards where the clock was, and treat month arithmetic as a rule you chose, since engines disagree on what 31 January plus a month is.
- 43As-of joins: the value that was true at the timeA fact that changes must be joined as of the moment the other row happened, through an effective-dated table with half-open periods that you have checked for overlaps and gaps, or through a latest-before lookup.
Module 5
Data that arrives dirty
No table you are handed was made for the question you have. This block is the craft of getting a file into a database without losing a row, turning text that people typed into typed columns, deciding what a duplicate and a missing value mean, and checking the result with queries that fail loudly. It ends with the three lessons that Google's and Kaggle's curricula treat as most of the job: encoding categories, building a feature table for a model, and keeping the future out of it.
By the end you can
Load an exported CSV with mixed encodings, ambiguous dates and duplicate rows into a typed, deduplicated table by re-runnable query with every dropped row counted, decide and document what each missing value means, and produce a feature table for a model in which no column knows anything that happened after its row's timestamp
- 44Cleaning real data without destroying itKeep the raw data, clean it in a query you can re-run, and count every row you drop.
- 45Getting a file in without losing a rowCount lines before and rows after every load, and state the delimiter, quoting, header and encoding rather than letting the loader guess, because a wrong guess drops or splits rows without an error.
- 46Stage as text, then cast in a query you can re-runLoad every column as text into a raw table that is never updated, then cast in a view beside a query that lists and counts every value the cast would refuse, because a typed load has to be right about every row before any row is in.
- 47Parsing dates that were typed by peopleA slash-separated date is ambiguous unless some value has a day above 12, so test the column before choosing a pattern, parse in a view, and check the minimum, maximum and month histogram afterwards.
- 48Text that looks identical and is notTwo strings that look identical can differ by invisible whitespace, case, or the Unicode form of an accent; build a normalised key column for grouping and joining and keep the original for display.
- 49Duplicates: finding them, and deciding which row survivesFind duplicates with GROUP BY and HAVING, keep one with row_number over a stated ordering and a tiebreaker, count what you dropped, and treat fuzzy matching as a judgement whose false merges cost more than its misses.
- 50Assertions: queries that return no rows when everything is rightEncode each thing that must be true as a query that returns its violations, run the whole file after every load, and include a size-and-distribution comparison with the previous load, because row-level checks cannot see a file that is silently half missing.
- 51Missing values: NULL, empty, N/A, zero and minus oneSentinels like empty strings, N/A, zero and minus one are values a query will happily average, so convert them to NULL, keep a flag for missingness, and fill only in the feature table, because every fill shrinks variance or invents a cluster.
- 52Categories: canonical values, mapping tables and encoding for a modelClean a category with a mapping table that a LEFT JOIN can expose gaps in, choose the encoding by cardinality and by whether the values have an order, and never give a model integer codes for categories that are not ordered.
- 53A feature table: one row per entity, as of a momentA feature table has one row per entity per as-of moment, every feature computed from strictly before that moment and the label from strictly after, built by one parameterised query that also serves predictions so training and live features cannot drift apart.
- 54Leakage: when a feature knows the futureLeakage enters a feature table through an unbounded aggregate, a join to a current value, a column written after the outcome, a target encoding that includes the row itself, or a split that puts one entity on both sides; a timestamp check catches the first two and a person who knows the source systems catches the rest.
- 55Why a query is slow, and what an index doesAn index lets the database skip rows; anything that hides a column's raw value takes that ability away.
Module 6
Changing data safely
Everything so far has read. This block writes: tables whose constraints refuse bad rows, inserts that can be run twice without duplicating, updates and deletes rehearsed as SELECTs, transactions that make a mistake reversible, two people writing the same row at once, schema changes on a table that is in use, views, JSON columns, history tables, and who is allowed to do any of it. It ends by designing a small schema from a paragraph of requirements.
By the end you can
Design and create a small schema whose keys and constraints refuse bad data, insert, update and delete inside a transaction you have rehearsed and can roll back, explain what happens when two writers touch the same row, alter a table that is in use without an outage, and give an analyst or an AI a read-only path into the database
- 56CREATE TABLE: a constraint is a test that runs on every writeA constraint is a test the database runs on every write, refusing the row rather than flagging it later; name each one, and know that CHECK sees one row at a time, so multi-row rules need UNIQUE, EXCLUDE or a trigger.
- 57INSERT, and the INSERT you can run twiceAn INSERT that must be safe to re-run needs a unique key and an ON CONFLICT clause, because the database detects the duplicate by inserting into the unique index, and without that index it cannot detect one at all.
- 58UPDATE and DELETE: rehearse it as a SELECTRun the WHERE clause as a SELECT, read the rows and the count, then change the first word; for UPDATE FROM, also prove that no target row matches more than once, because the database will pick one silently.
- 59Transactions: making a mistake reversibleA transaction makes several writes one unit and makes any of them reversible until COMMIT; know whether your tool autocommits, and remember that in Postgres one failed statement aborts the whole block until ROLLBACK.
- 60Two people, one row: lost updates and how the database prevents themA read followed by a write is a gap another transaction can enter; close it with an atomic UPDATE that reads and writes in one statement, a FOR UPDATE lock, or a version check that detects the collision and retries.
- 61Changing a table that is in useAdd nullable, backfill in batches, then constrain, because adding NOT NULL or an expression default rewrites or scans a live table under the strongest lock; set a lock timeout so a queued ALTER cannot take the site down behind one long report.
- 62Views: a saved question, not a saved answerA view stores a question and re-runs it on every reference, so it is always current and never faster than its query; a materialised view stores the answer, is as stale as its last refresh, and must say so.
- 63JSON in a column: when it helps and what it costsA JSON column trades every constraint for flexibility, so keys can be misspelt and types can drift on every insert; keep the document, promote the keys you query into generated columns with real types, and profile the keys before trusting them.
- 64Keeping history: soft deletes, audit rows and who changed whatAn UPDATE destroys the old value and a DELETE destroys the row, so decide up front whether a table needs a deleted_at flag behind a filtering view, an audit table filled by a trigger, or an effective-dated design for as-of questions.
- 65Who may do what: roles, GRANT and a read-only doorCreate a role for each kind of access with the least it needs, remember ALTER DEFAULT PRIVILEGES for future tables, and give an analyst or an AI a read-only role with a statement timeout and views that hide personal columns.
- 66From a paragraph to a schema: a tuition centreTurn requirements into a schema by giving every noun a table with a stated grain, every many-to-many its bridge table with its own facts, every implied rule a constraint, and every derivable number a query rather than a column.