Engineering practice · 25 MIN

Make running totals deterministic

Specify the window frame instead of relying on a default.

A running total needs both an order and a frame. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW accumulates physical rows in the chosen order. A unique id breaks ties. Default RANGE frames may include all peers at once, which surprises readers when sort values tie. Treat a null amount as zero only when that matches the business contract.

Treat the function as a small service: define a contract, maintain an invariant, and test the boundaries.

Read the example

SELECT id, COALESCE(amount,0) FROM sales ORDER BY id;

[[1,10],[2,10],[3,5],[4,8],[5,0]]

Your challenge

Return id, customer, running_total within each customer, ordered by id. Accumulate amounts by ascending id; null contributes zero.

Solution cost: Typically O(n log n), or closer to O(n) with suitable ordered access; verify the plan. time · Engine dependent, up to O(n) sort workspace. space

Common trap

A global window mixes totals from different customers.

Further reading: SQLite window frames