How are window functions different from GROUP BY for feature engineering?

GROUP BY collapses rows into a smaller summary table, so you usually have to join the result back to your examples. A window function computes the summary over related rows and writes the answer onto each row. That makes it natural for feature creation, where the training table must preserve the original example grain.

Why do window functions often cause data leakage?

They can see rows later in the ordered partition unless the ORDER BY and frame are chosen carefully. For prediction features, the window should usually include only facts known at the observation time. If a rolling total, rank, or previous-value feature includes future events, offline model performance can look better than real production performance.

What should I put in the ORDER BY clause?

Use the event or observation time that defines what came before the prediction, and add a stable tie-breaker when timestamps can repeat. Without deterministic ordering, lag, rank, and rolling features may change between runs. If the business event time and ingestion time differ, it depends which one represents knowledge available to the model.

Are window functions always the best way to build features?

No. They are excellent for auditable batch features over relational data, but they can be expensive on huge histories or low-cardinality partitions. Pre-aggregated tables, streaming feature pipelines, or specialised feature stores may be better when freshness, latency, or scale dominates. The right choice depends on data volume, scoring mode, and reuse requirements.

Window Functions for Features

Window functions for features are SQL expressions that compute per-example values from neighbouring or related rows without collapsing the result set. They partition records by an entity, order them by time or sequence, and apply functions over a defined frame to produce lag, rolling, rank, and time-bounded machine learning features.

Feature tables often need one row per prediction example, but the useful signal lives in the surrounding history: earlier purchases, prior sessions, recent failed payments, or an item’s position in a user’s sequence. A plain GROUP BY summarises that history by reducing many rows into fewer rows, which can destroy the example grain. Window functions solve this by reading across related rows while still returning a value on each original row.

A window function is evaluated over a window described in the OVER clause. PARTITION BY selects the peer group, such as all rows for a user or account. ORDER BY gives those rows a sequence, usually event time plus a deterministic tie-breaker. The frame then limits which rows are visible to the calculation. LAG reads a previous row, rolling aggregates count or sum recent rows, and rank functions assign positions within the ordered partition.

The main risk is leakage: accidentally letting a feature see information that would not have existed at prediction time. The fix is not just syntactic; it is defining the observation time and making the frame end at, or before, the current example according to the feature meaning. The cost is compute and memory pressure, especially with very large partitions, broad unbounded frames, or poor physical ordering of the underlying data.

Engineers meet these functions when turning event logs, transactions, sessions, and user-day tables into training or batch scoring features. Common examples include previous transaction amount, purchases in the last 7 days, days since last login, first-event flags, and best-so-far ranks. In practice, the query should filter to the relevant population, sort or cluster by entity and time where possible, and use the same logic for training and scoring.

Go deeper

Everything DataWindow Functions for FeaturesUse window functions to compute rolling counts, lag features, rank features, and time-bounded aggregates for ML examples.Open the lesson

Common questions

How are window functions different from GROUP BY for feature engineering?: GROUP BY collapses rows into a smaller summary table, so you usually have to join the result back to your examples. A window function computes the summary over related rows and writes the answer onto each row. That makes it natural for feature creation, where the training table must preserve the original example grain.
Why do window functions often cause data leakage?: They can see rows later in the ordered partition unless the ORDER BY and frame are chosen carefully. For prediction features, the window should usually include only facts known at the observation time. If a rolling total, rank, or previous-value feature includes future events, offline model performance can look better than real production performance.
What should I put in the ORDER BY clause?: Use the event or observation time that defines what came before the prediction, and add a stable tie-breaker when timestamps can repeat. Without deterministic ordering, lag, rank, and rolling features may change between runs. If the business event time and ingestion time differ, it depends which one represents knowledge available to the model.
Are window functions always the best way to build features?: No. They are excellent for auditable batch features over relational data, but they can be expensive on huge histories or low-cardinality partitions. Pre-aggregated tables, streaming feature pipelines, or specialised feature stores may be better when freshness, latency, or scale dominates. The right choice depends on data volume, scoring mode, and reuse requirements.

Every term