SQL & Databases
Topic 14 · SQL for machine learning · ⏱ about 6 h

SQL for Machine Learning

Getting training data out of databases: queries, joins, aggregation, window functions and leak-free features.

Show:
In this topic
Beginner

Why ML engineers write SQL every day

In tutorials, data arrives as a tidy CSV file. In a real organisation it lives in databases: a booking system, a payments system, a data warehouse. Before any model can be trained, someone has to write a query that pulls the right rows, joins the right tables and computes the first features. For junior ML engineers that someone is usually you, and SQL questions appear in almost every data and ML interview.

A relational database stores data in tables: each row is one record (one appointment, one patient) and each column is one attribute. Tables are linked by keys: an appointments table stores a patient_id that points to a row in the patients table. SQL (Structured Query Language) is the language for asking questions of those tables.

The basic query has a fixed shape. SELECT names the columns you want, FROM names the table, WHERE keeps only the rows that satisfy a condition, ORDER BY sorts and LIMIT keeps the first few rows:

SELECT appt_id, appt_date, status FROM appointments WHERE status = 'no_show' AND appt_date >= '2025-01-01' ORDER BY appt_date DESC LIMIT 10;

One surprise for beginners is that SQL is declarative: you describe the result you want, and the database decides how to compute it. Another is that clauses are not executed in the order they are written. The database logically runs FROM first, then WHERE, then GROUP BY, HAVING, SELECT, ORDER BY and finally LIMIT. That explains rules that otherwise look arbitrary, such as why a column alias defined in SELECT cannot be used in WHERE: when WHERE runs, the alias does not exist yet.

Try it

Try it: watch a query run clause by clause

Pick a query and step through it. Each step shows the intermediate table after one clause, in the order the database logically executes them. Rows that a clause removes are struck through; groups are coloured.

Interactive: this part runs in your browser.

Beginner

Aggregation and joins

Aggregation turns many rows into one summary per group. GROUP BY clinic_id splits the rows into one group per clinic, and aggregate functions such as COUNT(*), SUM, AVG, MIN and MAX summarise each group. A very common ML pattern computes a rate as the average of a 0/1 expression: AVG(CASE WHEN status = 'no_show' THEN 1.0 ELSE 0 END) is the no-show rate. WHERE filters rows before grouping; HAVING filters groups after it, for example HAVING COUNT(*) >= 100 to ignore clinics with too little data.

Joins combine tables. An INNER JOIN keeps only rows that have a match in both tables. A LEFT JOIN keeps every row of the left table and fills the right table's columns with NULL when there is no match. The difference matters enormously for training data: if a new clinic has not yet been added to the clinics table, an inner join silently drops all of its appointments, and your model never sees them. Always ask "what happens to rows without a match?", and compare row counts before and after each join.

The other classic join bug is fan-out. If you join appointments to a table that has several rows per patient (say, one per phone number), each appointment is duplicated once per matching row. Counts, averages and labels all become wrong without any error message. The fix is to join on a key that is unique on the other side, or to aggregate that table to one row per key before joining.

Try it

Try it: INNER vs LEFT joins, and fan-out

Two small tables. Choose the join type and the right-hand table, and see exactly which rows survive, which get NULLs and which get duplicated.

Interactive: this part runs in your browser.

Try it

Code example: no-show rate by clinic

A worked example: read it step by step (every part is explained), then run it and experiment.

Interactive: this part runs in your browser.

Beginner

Window functions, CTEs and leak-free features

Window functions compute a value for each row from a set of related rows (its window), without collapsing the rows the way GROUP BY does. The syntax is function(...) OVER (PARTITION BY ... ORDER BY ... frame). PARTITION BY patient_id restricts the window to the same patient; ORDER BY appt_date orders it in time; the frame says which rows count. Useful functions include ROW_NUMBER(), RANK(), LAG(col) (the previous row's value), LEAD(col) (the next row's value) and running aggregates like SUM(...) OVER (...).

For ML, windows are how you build history features in the database: a patient's previous no-shows, days since their last visit, their average lead time so far. The frame clause is where leakage hides. SUM(no_show) OVER (PARTITION BY patient_id ORDER BY appt_date) uses the default frame, which includes the current row: every appointment's feature would contain its own label. The correct frame stops just before the current row: ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. Using LEAD, or any frame that reaches forward, would leak the future.

Common table expressions (CTEs) make long queries readable. WITH clean AS (...), features AS (...) SELECT ... FROM features names each intermediate step, so the query reads top to bottom like a pipeline, and each step can be checked on its own.

Try it

Code example: a CTE and a window function

A worked example: read it step by step (every part is explained), then run it and experiment.

Interactive: this part runs in your browser.

Intermediate · Maths

The algebra behind queries

SQL is built on relational algebra, a small set of operations on tables (relations). Selection σc(R)\sigma_{c}(R) keeps the rows of RR satisfying condition cc (that is WHERE). Projection πa,b(R)\pi_{a,b}(R) keeps columns aa and bb (that is SELECT a, b). The join R⋈θSR \bowtie_{\theta} S is the Cartesian product R×SR\times S filtered by condition θ\theta.

The product view explains fan-out exactly. If key kk appears nR(k)n_R(k) times in RR and nS(k)n_S(k) times in SS, an inner join on kk returns

∣R⋈S∣=∑knR(k) nS(k)|R \bowtie S| = \sum_{k} n_R(k)\, n_S(k)

rows. If the key is unique in SS (nS(k)≤1n_S(k)\le 1), the result has at most ∣R∣|R| rows; any nS(k)=2n_S(k) = 2 doubles those rows. A left join adds back every unmatched row of RR: ∣R⟕S∣=∑knR(k)max⁡(1,nS(k))|R ⟕ S| = \sum_k n_R(k)\max(1, n_S(k)).

SQL uses three-valued logic: a comparison with NULL is neither true nor false but unknown, and WHERE keeps only rows where the condition is true. So WHERE age <> 30 silently drops rows where age is NULL, and NULL = NULL is not true; use IS NULL. Aggregates skip NULLs: AVG(x) averages only the non-missing values, which is itself a kind of imputation you should be aware of.

Advanced

Advanced: point-in-time correctness, performance and where to compute features

Point-in-time (as-of) joins. Features must reflect what was known at prediction time. If a patient's address changed last month, a training row from last year must use last year's address. Warehouses keep history with valid-from / valid-to columns, and the join condition becomes a.appt_date BETWEEN h.valid_from AND h.valid_to. Feature stores exist largely to make these as-of joins safe and consistent between training and serving.

Performance. On large tables, filter early (on partitioned or indexed columns such as dates), select only the columns you need, and avoid functions on indexed columns in WHERE (WHERE date(appt_date) = ... can prevent index use). Read the query plan (EXPLAIN) when a query is slow. Pull aggregated data rather than millions of raw rows when the model only needs aggregates.

SQL or Python? Compute features in SQL when they are joins, filters, aggregations or windows over large data: the database is fast and the logic is close to the source. Compute them in Python when they need libraries (text processing, embeddings), complex logic or iteration. Either way, version the query alongside the model code, and test it: row counts, uniqueness of keys and the range of each feature.

Code

Code: SQL from Python

The pattern used throughout the course and the game: a connection, a query string, and pandas.read_sql.

Python · sqlite3 + pandas
import sqlite3
import pandas as pd

conn = sqlite3.connect("clinic.db")          # or a SQLAlchemy engine for Postgres, Snowflake, BigQuery...

query = """
WITH visits AS (
    SELECT a.appt_id, a.patient_id, a.appt_date, a.status,
           CAST(julianday(a.appt_date) - julianday(a.booked_date) AS INTEGER) AS lead_days,
           c.name AS clinic_name
    FROM appointments a
    LEFT JOIN clinics c ON c.clinic_id = a.clinic_id      -- keep appointments at unknown clinics
    WHERE a.status <> 'cancelled'
)
SELECT appt_id, patient_id, appt_date, lead_days, clinic_name,
       COALESCE(SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) OVER (
           PARTITION BY patient_id ORDER BY appt_date, appt_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prior_noshows,   -- past only
       CASE WHEN status = 'no_show' THEN 1 ELSE 0 END AS no_show
FROM visits
WHERE appt_date >= :start
"""
df = pd.read_sql(query, conn, params={"start": "2024-01-01"})   # parameters, never string formatting

assert df["appt_id"].is_unique, "fan-out: a join duplicated rows"
print(df.shape, df["no_show"].mean())
Open in playground ↗
Before it runs, the course writes the synthetic clinic database to clinic.db.
Try it

Cheat sheet: SQL

The commands you will use most, on one screen. Come back to it while you work.

Interactive: this part runs in your browser.

Beginner

Summary

Key words

feature table
one row per example, built with SQL for a model
point-in-time
only using data that existed at prediction time
leakage
information from the future sneaking into features

Common mistakes

  • Joining data from after the prediction date (leakage).
  • Fan-out in joins that duplicates training rows.
  • Random splits of time-ordered data.
Check yourself

Quiz

1. In which order does the database logically process these clauses?

2. Appointments at a clinic missing from the clinics table disappear from your result. The likely cause is…

3. You want clinics with at least 100 appointments. Which clause filters the groups?

4. Which frame computes a patient's previous no-shows without leakage?

5. A key appears 3 times in table A and 2 times in table B. An inner join on it returns how many rows for that key?

6. WHERE age <> 30 on a table where some ages are NULL…

Go deeper

📚 Further reading

Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.

Ready