SQL & Data Engineering for ML · 55 min · DuckDB · PostgreSQL

Window Functions for Features

Window functions are how SQL does time. Most leakage-free temporal features start here.

Hiring signal: Builds time-aware features without target leakage

What you will learn

Use OVER, PARTITION BY, and ORDER BY to compute per-group windows
Rank rows with ROW_NUMBER, RANK, and DENSE_RANK
Build time-series features with LAG and LEAD
Compute rolling aggregates and moving averages
Prevent target leakage when windowing over time

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 It, Use It, Ship It, Evaluation, Exercises, Key Terms, Common Pitfalls, Interview Framing — plus a hands-on lab, quiz, and project artifact.

Create a free account to unlock Phase 0 and Phase 1 of every course — no credit card.