A running total over a date column jumps on tied dates — ROWS vs RANGE
The running total should climb one row at a time, but rows that share a sale_date all show the same jumped total instead of stepping up individually. Explain why and fix it.
SELECT sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;Diagnose and fix the bug.
The default RANGE frame's CURRENT ROW covers every peer sharing the same sale_date, so a tied date shows the total through that whole date, not a per-row step. Fix it with ROWS plus a unique tiebreaker like id.
- ✗Blaming duplicate rows instead of the
RANGEpeer semantics - ✗Believing
ROWSandRANGEbehave identically - ✗Adding
PARTITION BY sale_date, which resets the total per date
- →How does
RANGEdefine a peer, and why does that matter here? - →What does adding a unique
idto theORDER BYguarantee?
The cause is not duplicates but the difference between ROWS and RANGE. A window with ORDER BY defaults to RANGE ... AND CURRENT ROW, and in RANGE mode "current row" means every peer row sharing the same sale_date. So all rows of one date get a total that includes all their peers at once.
Switch to ROWS (which counts physical rows) and add a unique tiebreaker so the order is deterministic:
SELECT sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date, id
ROWS UNBOUNDED PRECEDING) AS running_total
FROM sales;PARTITION BY sale_date would not help — it would reset the total at the start of each date. DISTINCT and DESC are also irrelevant.
