02.03 · Walkthrough
Window Functions for Features
Use window functions to compute rolling counts, lag features, rank features, and time-bounded aggregates for ML examples.
Window functions let SQL feature queries add history-aware values to each example row without collapsing the dataset. They are useful for lagged behaviour, rolling activity, ranks within an entity, and bounded lookback aggregates, provided the query orders events correctly and only uses information available at prediction time.
What this lesson answers
- how to build lag features in SQL
- SQL rolling counts without grouping rows
- avoid data leakage with window functions
Notes
Window functions are a practical way to create machine learning features directly in SQL while preserving one row per example. Unlike a GROUP BY, which collapses rows, a window function looks across a related set of rows and writes the result back onto each row. This is especially useful when each training example represents an event, transaction, session, or user-day, and you want features such as “number of purchases in the last 7 days,” “previous transaction amount,” “days since last login,” or “rank of this item within the user’s history.” The key ideas are partitioning, which defines the…
Common questions
- Why use window functions instead of GROUP BY for ML features?
- GROUP BY reduces many input rows into fewer output rows, which is often wrong when each event or user-day must remain a separate training example. A window function can calculate values over related rows, such as prior activity for the same user, then attach the result back to every original example row.
- How do window functions cause data leakage?
- Leakage happens when the window includes rows that would not have been known at the prediction point. The usual fix is to partition by the entity, order by event time, and define the frame so it only sees the allowed history. Tie-breaking in the ordering also matters when timestamps are not unique.
- Are SQL window features safe to run on large event tables?
- They can be, but unbounded windows and very large partitions can become expensive. Filter to the relevant examples, keep partitions aligned with the entity used for prediction, and prefer bounded history when the feature definition allows it. Sorting or clustering by entity and time can also make these queries more practical.
Short definition: what is Window Functions for Features?