Window Frames (ROWS, RANGE, GROUPS) in SQL

The window frame clause controls exactly which rows feed into a window function's calculation for each row. It's a sliding boundary that moves as the function processes the partition.

You've already used window functions that compute running totals: SUM(revenue) OVER (PARTITION BY region ORDER BY sale_date). What you may not have noticed is that a default frame is silently active. When you include ORDER BY inside OVER, the frame automatically covers from the start of the partition through the current row. That's why running totals accumulate correctly without any extra syntax.

Once you want a rolling window — the last 7 days, the last N rows, a specific date range — you need to declare the frame explicitly. ROWS and RANGE are the two modes you'll use in practice.

ROWS counts physical row positions. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means: the current row plus exactly the 6 rows before it in the sorted partition.

If the data has one row per day, this is a 7-day rolling sum. But if there are gaps in the date sequence — a day with no data — ROWS still grabs exactly 6 prior rows regardless of their dates. You'd be summing across a longer calendar window than you intended.

RANGE operates on values, not positions. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW includes all rows whose date falls within the 6 calendar days before the current row's date.

Gaps are handled correctly: missing days contribute nothing. The window always represents a true 7-day calendar range regardless of how many rows it contains.

With UNBOUNDED PRECEDING, ROWS and RANGE produce identical results on this data — you can confirm it:

The one thing that trips people up

ROWS and RANGE produce identical results when there are no gaps and one row per date. They diverge the moment gaps appear — and they diverge silently. Both queries run without error, but one returns the wrong values.

Use RANGE when gaps in the data matter and you want a true calendar window. Use ROWS when you need a fixed number of observations regardless of what dates they cover.

UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING

These boundary keywords cover the full partition. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING covers every row for every calculation. This is the frame you need for LAST_VALUE and NTH_VALUE to behave as expected, and for any aggregate that should look at the entire group rather than a sliding window.

GROUPS is a third frame mode that counts peer groups (rows with equal ORDER BY values) rather than positions or value ranges. It's less common and covered when you encounter tied values in ranked windows.