Why do two cash events share the same running balance?

Prepare the diagnostic input

The ledger needs the balance after each event. SQLite’s default ordered aggregate frame groups equal ordering values as peers. A timestamp alone therefore gives both 09:00 events the balance after their combined changes. Include the sequence ID and an explicit ROWS frame.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE cash_events(id INTEGER PRIMARY KEY,event_at TEXT,delta_cents INTEGER);
INSERT INTO cash_events VALUES (1,'2026-10-10 09:00',10000),(2,'2026-10-10 09:00',-3000),(3,'2026-10-10 10:00',-2000);

Reproduce the misleading result and correct it

SELECT id,SUM(delta_cents) OVER(ORDER BY event_at) AS balance_cents FROM cash_events ORDER BY id;

SELECT id,SUM(delta_cents) OVER(ORDER BY event_at,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance_cents
FROM cash_events ORDER BY id;

After receipt 1, cash is 10000 cents; after expense 2, it is 7000; after expense 3, 5000. The wrong query starts at 7000 for both first events, hiding the intermediate receipt balance. No money is moved by either query.

Check the boundary before applying the fix

Add a later-ID receipt at the same 10:00 timestamp. Event 3 remains 5000, and event 4 reaches 6000; same-time entries still have a defined sequence.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO cash_events VALUES (4,'2026-10-10 10:00',1000);

The ID order is a fixture policy. A real ledger needs an approved posting sequence and opening balance; arbitrary IDs do not prove actual chronology.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.