Running total per account

SELECT account_id,
       occurred_at,
       amount,
       SUM(amount) OVER (
         PARTITION BY account_id
         ORDER BY occurred_at, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
  FROM transactions;

PARTITION BY restarts the accumulation per account — without it you get one running total across every account mixed together.

The frame clause is where this goes wrong silently. The default frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes all peer rows — every row with the same occurred_at. So two transactions at the same instant both show the total including both. That is often not what is wanted, and because it only manifests on ties, it survives testing on clean data and appears in production.

ROWS counts physical rows instead and gives a true row-by-row accumulation. Writing the frame explicitly is the habit worth having.

Ties need a deterministic tiebreaker regardless. Adding id to the ORDER BY makes the result stable; without it, two runs of the same query can legitimately return different running balances for tied rows.

Why not a self-join. SUM over all rows with an earlier timestamp is O(n²) and will not survive a real table. The window function is one pass over a sorted partition.