Problem Statement
What pitfalls arise from using RANGE frames with duplicate ORDER BY values, and how do you avoid them?
Explanation
RANGE frames operate on value ranges, not physical row counts. When many rows share the same ORDER BY value, the frame can expand to include all peers. Running totals may jump by batches, and averages might include more rows than you expect.
To avoid surprises, use ROWS frames for precise row counts, or include a tiebreaker in ORDER BY like an id column to create a strict ordering. For first and last value, specify an explicit ROWS frame that covers exactly the rows you want.
Code Solution
SolutionRead Only
SUM(amount) OVER ( ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS safe_running
