SQL for Data and ML Work: Joins, Aggregation, and Windows

Stop thinking in spreadsheets

Most engineers arrive at SQL with a spreadsheet in their head: rows you scroll through, cells you point at. SQL is set-based and declarative. You describe the result you want and the database decides how to produce it. The sooner you stop picturing a cursor walking down the rows, the sooner queries start to feel obvious. For ML work this is the whole ballgame, because your features almost always begin as a query against a warehouse, and the shape of that query decides what your model is even allowed to see.

Joins are the whole game

If you're shaky on one thing, it's joins, and they cause more silent bugs than anything else in SQL. An INNER JOIN keeps only matching rows; a LEFT JOIN keeps every row on the left and fills nulls where the right has no match. That one difference quietly changes your row counts, and therefore the label balance your model trains on.

The trap is fan-out. Join a table of users to a table of events and you no longer have one row per user; you have one row per event. Average a per-user column now and the result is weighted by how many events each user had, which is not the number you meant to compute. Always know the grain of your result: what does a single row represent? A quick COUNT(*) before and after a join catches most of these before they reach a model.

GROUP BY, precisely

Aggregation collapses many rows into one per group, and it's the core of feature engineering. GROUP BY user_id with COUNT(*), SUM(amount), and AVG(score) is bread and butter. Two rules keep you out of trouble. Every non-aggregated column in the SELECT has to appear in the GROUP BY. And WHERE filters rows before grouping while HAVING filters groups after, so reaching for HAVING when you meant WHERE makes the database scan far more data than the question required.

Window functions are the feature engineer's power tool

This is the part that turns a competent SQL user into a genuinely useful one. Window functions compute across a set of rows related to the current row without collapsing them. ROW_NUMBER() and RANK() over PARTITION BY user_id ORDER BY event_time let you grab the first or most recent event per user. LAG() and LEAD() give you the previous and next value in a partition, which is how a feature like days since last purchase gets built. SUM(...) OVER (PARTITION BY ... ORDER BY ...) produces running totals.

Nearly every temporal feature a model wants (recency, frequency, a rolling average) is a window function underneath. Learn these and you'll stop exporting raw data into pandas just to compute things the database can do in a single pass, closer to where the data already lives.

Get the shape right before you optimize

Write the query for the next person to read it, which is usually you a month from now. Common Table Expressions (WITH step AS (...)) let you name intermediate results so a sixty-line query reads top to bottom instead of nesting into a pyramid of subqueries. Leave indexes and query hints alone until a query is actually slow; when it is, EXPLAIN shows you what the database is doing, and the usual culprit is a join on an unindexed column or a full scan you didn't intend to ask for.

SQL or pandas? Push the work down.

A decision you'll make constantly is whether to transform data in SQL or pull it into pandas and do it there. Default to SQL, close to the data. The warehouse is built to filter, join, and aggregate at scale; your laptop's memory is not. Pull down the smallest result you can and let pandas handle the last mile, the plotting or the model-specific reshape that SQL is genuinely clumsy at. Engineers who ship reliable pipelines tend to push work down into the database rather than up into Python.

Where to take this next

The free SQL for AI/ML Production course drills exactly these patterns, joins and aggregation and window functions, against a realistic warehouse, one query at a time with your output checked as you go. It pairs well with Python Fundamentals if the pandas half of your workflow is also shaky. And if you'd rather have a mentor watch what you're building and hand you the next query worth mastering, join the waitlist.