The Problem
"What was each user's previous purchase amount?" "How many logins in the trailing 7 days?" "Rank products by revenue within each category." These are the features that actually move ML models — and they're nearly impossible with plain GROUP BY, which collapses rows and forgets order. Window functions compute across related rows while keeping every row, which is exactly what time-aware feature engineering needs.
They're also the SQL topic most likely to separate a junior from a mid-level candidate in a take-home.
The Concept
The Problem with GROUP BY for Time-Aware Features
GROUP BY collapses rows: if you group by user_id, you get one row per user — all the individual events are gone. But ML features often need both the aggregate and the individual row: "this user's current purchase amount, plus their rolling 7-day total, plus their rank among all users." You can't get that with GROUP BY alone — you'd need a subquery and a join back to the original table.
Window functions solve this by computing over a "window" of rows defined relative to the current row, without collapsing them. Every row in the output has its own window, and the function computes using the rows in that window. This is what makes window functions the natural tool for temporal features: "previous purchase amount" (LAG), "7-day rolling sum" (SUM OVER ROWS PRECEDING), "rank within category" (RANK OVER PARTITION BY).
func(...) OVER (
PARTITION BY <group> -- reset the window per group (e.g., per user)
ORDER BY <sort> -- order within the partition (e.g., by time)
<frame> -- which rows around current (e.g., last 7)
)
GROUP BY returns one row per group. A window function returns every row, annotated with a value computed from its window. That distinction is the whole idea — and it's what makes window functions essential for leakage-free temporal feature engineering.
You want to add a column showing each user's rank by total spend, but keep all individual rows. Do you use GROUP BY or a window function?
GROUP BY collapses rows into one per group — you'd lose the individual purchase records. A window function like RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) annotates each row with its rank while keeping every row visible.
We use: events(user_id, event_type, amount, created_at).
Unlock the full lesson
You've read the first 2 sections. The rest of this lesson covers Build, Check Yourself, Key Terms & Next — plus a hands-on lab, quiz, and project artifact.
Create a free account — the opening phases of 29 of 35 courses are free, no credit card.