Recursive Running Balance Per Account

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a Transactions table. Write a SQL query to compute the running balance for each account, in chronological order of transaction date (ties broken by txn_id). Each row should show the account, date, the transaction amount, and the cumulative running balance up to and including that transaction. Return (account_id, txn_date, amount, running_balance) ordered by account_id, then txn_date, then txn_id. IMPORTANT: Use a recursive CTE (not a window function SUM OVER) to compute the running balance. Table: Transactions

Column Name	Type	Description
txn_id	INT	Primary key
account_id	INT	Account identifier
amount	INT	Positive = credit, Negative = debit
txn_date	DATE	Transaction date

Database Schema (Inferred)

Transactions

Column Name	Example Value
txn_id	1
account_id	101
amount	5000
txn_date	2024-01-01

Example

Transactions

txn_id	account_id	amount	txn_date
1	101	5000	2024-01-01
2	101	-1500	2024-01-05
3	101	2000	2024-01-10
4	101	-500	2024-01-15
5	102	3000	2024-01-02
6	102	-1000	2024-01-08

Output

account_id	txn_date	amount	running_balance
101	2024-01-01	5000	5000
101	2024-01-05	-1500	3500
101	2024-01-10	2000	5500
101	2024-01-15	-500	5000
102	2024-01-02	3000	3000
102	2024-01-08	-1000	2000

Explanation:

Assign row numbers per account ordered by date/txn_id. Anchor at rn=1, recursively join next row (rn+1) and accumulate. Each recursive step adds current amount to parent's running_balance.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.