SQL for Machine Learning
Getting training data out of databases: queries, joins, aggregation, window functions and leak-free features.
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:
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: 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.
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: 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.
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.
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.
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.
The algebra behind queries
SQL is built on relational algebra, a small set of operations on tables (relations). Selection keeps the rows of satisfying condition (that is WHERE). Projection keeps columns and (that is SELECT a, b). The join is the Cartesian product filtered by condition .
The product view explains fan-out exactly. If key appears times in and times in , an inner join on returns
rows. If the key is unique in (), the result has at most rows; any doubles those rows. A left join adds back every unmatched row of : .
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: 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: SQL from Python
The pattern used throughout the course and the game: a connection, a query string, and pandas.read_sql.
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())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.
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.
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…
📚 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.
- SQLBolt ↗ free interactive SQL lessons in the browser
- SQLite: Window functions ↗ Official reference