Window Frames: Running & Rolling Totals

A partition can be big. Sometimes you only want part of it — "the last two matches" for a form guide, or "from the first match until today" for a season total so far. The frame clause picks that sliding sub-window around the current row.

Definition

A window frame is the subset of rows inside the current partition, measured relative to the current row, that the window function actually uses. It is written as ROWS BETWEEN <start> AND <end> (or RANGE BETWEEN …) after the ORDER BY inside OVER.

• Inside the partition — a frame never crosses into another player's partition.
• Relative to the current row — as SQL moves from row to row, the frame slides with it.
• Requires ORDER BY — "1 row before" only makes sense when the rows have an order. In SQL Server a frame without ORDER BY is an error.

Syntax

function(expression) OVER (
    [PARTITION BY ...]
    ORDER BY sort_column
    ROWS BETWEEN frame_start AND frame_end
)

The start must come before (or equal) the end. Short form: ROWS UNBOUNDED PRECEDING = ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Default frames — very important

Example — three frames, current row = id 3

Using all 12 innings ordered by id (one partition):

SELECT id, runs,
    SUM(runs) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS start_to_current,
    SUM(runs) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)          AS one_around,
    SUM(runs) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  AS current_to_end,
    SUM(runs) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)          AS last_two
FROM innings;

Check id 3: start → current = 82 + 59 + 73 = 214; one before → one after = 59 + 73 + 45 = 177; current → end = 73 + 45 + … + 107 = 504. At id 4: running total = 259, last two = 73 + 45 = 118.

Running total vs rolling total

Example — running total per player

SELECT id, player, match_date, runs,
    SUM(runs) OVER (
        PARTITION BY player
        ORDER BY match_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM innings;

Result: Kohli 82 → 141 → 214, Rohit 45 → 121 → 121, Dhoni 26 → 26 → 71, Buttler 73 → 132 → 239 (same numbers as the previous topic — here the frame is written explicitly).

Example — rolling total of the last 2 matches per player

SELECT id, player, match_date, runs,
    SUM(runs) OVER (
        PARTITION BY player
        ORDER BY match_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS rolling_2
FROM innings;

Step by step: (1) partition by player; (2) order by match date; (3) add only the previous row + the current row. Kohli's third value is 59 + 73 = 132 — his 82 has dropped out of the frame. The first row of each player has no previous row, so it is just its own runs.

Moving average

Swap SUM for AVG and you get a moving average — the classic way to smooth out ups and downs:

ROUND(AVG(runs * 1.0) OVER (
    PARTITION BY player
    ORDER BY match_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
), 1) AS moving_avg_2

Buttler: 73 → (73 + 59) ÷ 2 = 66 → (59 + 107) ÷ 2 = 83.

ROWS vs RANGE

• ROWS counts physical rows — "1 PRECEDING" is exactly one row back.
• RANGE works on values of the ORDER BY column — all rows with the same value (ties) are treated as one peer group and included together.

This matters with ties. Ordering by runs (two 45s, two 59s, two 73s):

With the default RANGE frame, both 45s show 116 at once. With ROWS, the first 45 shows 71. That is why running totals should use an explicit ROWS frame and a unique ORDER BY.

SQL Server note: RANGE only supports UNBOUNDED PRECEDING, CURRENT ROW and UNBOUNDED FOLLOWING — RANGE BETWEEN 1 PRECEDING … is not allowed. Use ROWS for "n rows" frames. ROWS is also usually faster.

Common mistakes

• ❌ Writing a frame without ORDER BY → ✅ frames require ORDER BY in OVER.
• ❌ Using a frame with ROW_NUMBER, RANK, NTILE, LAG or LEAD → ✅ frames are not allowed for those functions.
• ❌ Relying on the default RANGE frame when the ORDER BY column has ties → ✅ write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly.
• ❌ Putting the end before the start (ROWS BETWEEN CURRENT ROW AND 1 PRECEDING) → ✅ the start boundary must come first.

Interview tip

Q: How do you calculate a 3-match moving average in SQL? A: AVG(runs) OVER (PARTITION BY player ORDER BY match_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) — the current match plus the two before it.