Windows II — frames & running
Concept  ·  SQL & Querying

A running total resets where PARTITION BY says — omit the partition and one entity's total runs straight into the next.

ORDER BY inside a window defines sequence; PARTITION BY defines scope — and an unpartitioned running sum has global scope, so the accumulator never zeroes between entities. The cruelty is that the first entity is always correct (nothing precedes it), so spot checks pass and the drift begins at entity two. The scope question — 'when should this number return to zero?' — is the PARTITION BY clause, written in English first.

See it break
laps
tracklapriders
east164
east258
west158
west260
The trap
SELECT
  track,
  lap,
  SUM(riders) OVER (
    ORDER BY
      track,
      lap
  ) AS cumulative
FROM
  laps
ORDER BY
  track,
  lap;
The fix
SELECT
  track,
  lap,
  SUM(riders) OVER (
    PARTITION BY
      track
    ORDER BY
      lap
  ) AS cumulative
FROM
  laps
ORDER BY
  track,
  lap;
INPUTYour queryThe fix
east16464
east2122122
west118058
west2240118

The running total never reset between lines — without PARTITION BY, west's first cumulative showed 180 (east's 122 plus its own 58), where partitioning restarts each line at its own first lap.

The rule

Partition = the reset boundary, stated; verify on entity #2, never #1.

Shows up elsewhere
Sessionize by running-SUM of flagsDefault frame includes peer ties
Try one →Prove it in practice →