SQL rolling windows — ROWS vs RANGE frames

Start with the basics

A running total or rolling average is a window function with a frame — a rule for which rows around the current one the sum should see.

The frame can be counted two ways. ROWS counts a fixed number of rows back; RANGE counts by the ordering value itself, so 'the last 7 days' means days, not rows.

Most of the time the data has one row per day and the two agree. The traps all show up the moment the rows are uneven — gaps, ties, or several rows on one day.

One that works

SELECT day, SUM(amount) OVER (
  ORDER BY day
  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rolling
FROM txns
ORDER BY day;

Each row's rolling value sums itself and the row before it.

A window frame says which rows around the current one an aggregate sees — and ROWS counts rows while RANGE counts values.

When to use it

• ·Reach for a frame when you want a running total, a moving average, or a cumulative sum.
• ·Use ROWS for 'the last N rows'; use RANGE with an interval for 'the last N days'.
• ·If your dates have gaps or several rows per day, choose the frame deliberately — the two stop agreeing there.

That choice — count rows or count time — is where a rolling total quietly lies. The two agree only when the data is perfectly even; the day a gap appears, ROWS reaches back too far. Here is the day that bites.

See it happen

The rolling total that reaches too far — ROWS vs RANGE

You want a rolling 7-day spend total. One customer has a gap in their transactions. Watch how counting rows pulls in a transaction that is actually 8 days old.

SELECT txn_date, SUM(amount) OVER (
  ORDER BY txn_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling
FROM txns ORDER BY txn_date;

SELECT txn_date, SUM(amount) OVER (
  ORDER BY txn_date
  RANGE BETWEEN INTERVAL '6 days'
    PRECEDING AND CURRENT ROW
) AS rolling
FROM txns ORDER BY txn_date;

! Wrong by $10, no error raised, and every calendar gap makes it worse.

The rest of the family

More ways a running total goes wrong

Self-check

How to spot it in your own SQL

• →A rolling total looks slightly high, and worse where the dates have gaps.
• →A running total jumps at rows that share a value.
• →One entity's running total seems to continue into the next.
• →A moving average's first few points look wrong.

What people miss

Running totals feel solved once the query runs, but the frame is silent about what it includes. ROWS and RANGE agree on clean data and diverge on real data — exactly when nobody is checking.

Go deeper