Interview Questions/SQL/Recursive Running Balance Per Account

Recursive Running Balance Per Account

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

Medium

Recursive Running Balance Per Account

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

Examples

Example 1

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.

query.sql