SQL, data modeling, data quality, EDA, visualization · insight-0043
Specify ROWS and a unique ORDER BY in running totals, because the default frame groups ties
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A running total of bookings uses SUM(...) OVER (ORDER BY booking_date) and several rows share the same date.
Action
Write the frame explicitly (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) and add a tie-breaker to ORDER BY (date, booking_id). If you want one value per date, aggregate to the date first.
Reason
With ORDER BY and no frame clause, the default is RANGE from the partition start to the current row's last peer, so tied rows share a frame.
Boundary
Running totals, cumulative bookings, rank-based cutoffs and Pareto charts in SQLite and PostgreSQL.
Symptom or risk
All rows with the same date get the same, end-of-day running total, so row-level cumulative values and "first time reaching X" logic are wrong.
Exceptions
When per-date totals are the intent, the RANGE default gives them; document that choice.
Trade-offs
Explicit frames make queries longer but portable and reviewable.
Pitfalls
Omitting ORDER BY inside OVER, which makes every row a peer and returns the partition total.
Lessons and concepts
Claims and their sources
The default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; in RANGE mode CURRENT ROW includes all peers of the current row, and without ORDER BY all rows are peers.