Interview Question Bank · GROUP BY month · running SUM over groups
2. Month-end balance per account
MediumBankingQuestion #100
Concept: GROUP BY month · running SUM over groupsshow
A balance is the running total of every movement so far, not the sum of one period. Aggregate first to one row per account and month (the month's net change), then carry it forward with SUM(...) OVER (PARTITION BY account ORDER BY month). PARTITION BY restarts the total for each account; ORDER BY makes it cumulative.
Example (different data):
SELECT warehouse_id, moved_on, qty, SUM(qty) OVER (PARTITION BY warehouse_id ORDER BY moved_on) AS on_hand FROM stock_moves;
Your task
Copperleaf Bank's statements team needs each account's closing balance for every month it had activity. The ledger only stores individual deposits and withdrawals, and every account opened at zero.
From fin_ledger (amount is positive for deposits, negative for withdrawals), return account_id, month (to_char(posted_at, 'YYYY-MM')) and month_end_balance (the balance after every entry up to the end of that month) for each account and each month in which it had at least one entry. Sort by account_id, month.
Starting Postgres in your browser…
Checking your access…