Running Balance With Resets — Solution & ExplanationProblems / Running Balance With Resets / EditorialApproach

Sign and flag. Deposits positive, withdrawals negative, resets 0 — plus a 0/1 is_reset flag.

Segment via prefix sum. SUM(is_reset) over a running frame per account converts reset flags into a segment counter. The inclusive frame is a subtle gift: the reset row counts itself, so it opens its own segment and (contributing 0) shows the fresh 0.00 balance.

Run the sum within segments. The balance window partitions by (account_id, segment) — the partition boundary is what makes the sum "forget", because window functions cannot see across partitions at all.

Key InsightWindow functions accumulate; they don't conditionally *reset* — there is no SUM(...) RESTART WHEN clause. The universal workaround is to materialize the reset as a partition key: derive a segment id with the flag-prefix-sum trick, and the engine's own partition isolation does the forgetting. This two-window composition (one window to *define* groups, a second to *aggregate within* them) is the load-bearing pattern behind sessionization, streak analysis, and every "running X that restarts on Y" requirement.Hints

A running SUM has no forget button — once a deposit enters the frame it never leaves. To make the sum restart, change the PARTITION instead: give each between-resets stretch its own segment id.

The segment id is the flag-then-prefix-sum idiom (same as sessionization): is_reset as 0/1, running SUM per account. Every reset bumps the counter; rows after it inherit the new id.

Because the running count includes the current row, the reset row itself already belongs to the NEW segment — and with its signed amount of 0, its balance prints 0.00 with no special-casing.

Two windows, two grains: the segment counter partitions by account; the balance partitions by (account, segment). Consecutive resets and negative balances all fall out correctly.

Solve Running Balance With Resets yourself →