Windows II — frames & running
Concept  ·  SQL & Querying

ORDER BY with no explicit frame defaults to RANGE ... CURRENT ROW, which includes tied peers — running totals jump on tied keys; explicit ROWS is the bar for running sums.

An ORDER BY window with no explicit frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes every peer that ties on the ORDER BY key. So tied rows all see each other, and a running total leaps by the whole tie group at once.

See it break
scores
playertpts
A110
A120
A25
The trap
SELECT
  t,
  SUM(pts) OVER (
    ORDER BY
      t) AS running
FROM
  scores
ORDER BY
  t,
  pts;
The fix
SELECT
  t,
  SUM(pts) OVER (
    ORDER BY
      t,
      pts ROWS UNBOUNDED PRECEDING
  ) AS running
FROM
  scores
ORDER BY
  t,
  pts;
INPUTYour queryThe fix
13010
13030
23535

The running total jumps straight to 30 on the first tied row — the default RANGE frame swallowed the peer sharing its timestamp; ROWS adds them one at a time.

The rule

State ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you want a row-by-row running total, or add a tiebreaker to the ORDER BY so no rows are peers.

Shows up elsewhere
ROWS counts rows, RANGE counts time
Try one →Prove it in practice →