ORIGINAL TECHNICAL PRACTICE
Make a running total's frame explicit
Two transactions share a timestamp and a running sum jumps by both amounts at once. What would you inspect?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
The window frame and ordering determine which rows contribute. A peer-aware RANGE frame can include rows sharing the ordering value, while a ROWS frame follows positions in the specified order.
Work through it
- Inspect the database’s default frame and the query’s ordering keys. Decide whether equal timestamps should contribute together or in a deterministic transaction sequence.
- For a per-transaction running total, use a stable secondary key and an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame.
- Test duplicate timestamps, negative adjustments and partition boundaries. Confirm whether the result should restart per account, currency or another entity.
Tradeoffs to discuss
- A timestamp-only cumulative balance may intentionally treat simultaneous events as a group. A row-by-row balance needs a meaningful total order and can expose ingestion-order assumptions.
Common pitfalls
- Window ORDER BY does not guarantee final output order without an outer ORDER BY. An arbitrary tie order can make intermediate balances vary even when the final total is correct.