Current Balance From the Account Ledger — SQL Joins Problem
Core banking does not trust a stored balance: it recomputes it from the ledger.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #57
Problem statement
Core banking does not trust a stored balance: it recomputes it from the ledger. An account's current balance is its opening_balance plus every credit minus every debit, ignoring entries that were later reversed (is_reversed = 1). An account with no entries (or only reversed ones) has its opening balance.
Return account_id, holder_name and current_balance for every account, ordered by account_id.
Tables
Table: Account
| Column | Type |
|---|---|
| account_id | int |
| holder_name | varchar |
| opening_balance | int |
Primary key: account_id.
One row per account; opening_balance is in rupees.
Table: LedgerEntry
| Column | Type |
|---|---|
| entry_id | int |
| account_id | int |
| entry_type | enum(CREDIT, DEBIT) |
| amount | int |
| posted_on | date |
| is_reversed | bool |
Primary key: entry_id.
One row per posting; amount is always positive and entry_type gives its direction. account_id always names an account.
Examples
Example 1
Account
| account_id | holder_name | opening_balance |
|---|---|---|
| 1 | Saanvi Reddy | 10000 |
| 2 | Arjun Menon | 0 |
| 3 | Olivia Joshi | 25000 |
| 4 | Nikhil Bose | 5000 |
LedgerEntry
| entry_id | account_id | entry_type | amount | posted_on | is_reversed |
|---|---|---|---|---|---|
| 1 | 1 | CREDIT | 50000 | 2024-07-01 | 0 |
| 2 | 1 | DEBIT | 12000 | 2024-07-03 | 0 |
| 3 | 2 | CREDIT | 8000 | 2024-07-04 | 0 |
| 4 | 2 | DEBIT | 3000 | 2024-07-05 | 1 |
| 5 | 1 | DEBIT | 1500 | 2024-07-09 | 0 |
| 6 | 4 | CREDIT | 20000 | 2024-07-10 | 1 |
| 7 | 2 | DEBIT | 2500 | 2024-07-12 | 0 |
Output
| account_id | holder_name | current_balance |
|---|---|---|
| 1 | Saanvi Reddy | 46500 |
| 2 | Arjun Menon | 5500 |
| 3 | Olivia Joshi | 25000 |
| 4 | Nikhil Bose | 5000 |
How to solve Current Balance From the Account Ledger
A ledger stores amounts with a direction, so the first step is to give each entry a sign: CASE WHEN entry_type = 'CREDIT' THEN amount ELSE -amount END. Summing those per account gives the net movement, and adding it to the opening balance gives the current balance.
Every account must be reported, so the query starts from Account with a LEFT JOIN. Reversed entries are excluded in the ON clause: in the WHERE clause the test would also discard the NULL row of an account whose entries were all reversed, and that account would vanish. For an account with nothing left to join, SUM returns NULL and opening_balance + NULL is NULL — COALESCE(…, 0) keeps the opening balance instead.
Pre-aggregating the ledger in a derived table and joining it once is the plan a large bank would prefer (one pass over the ledger, then a join on a much smaller set); two correlated subqueries per account are the most literal reading. All three agree, including on accounts with no postings.
Reference solution (MySQL)
SELECT a.account_id, a.holder_name,
a.opening_balance + COALESCE(SUM(CASE WHEN l.entry_type = 'CREDIT' THEN l.amount ELSE -l.amount END), 0) AS current_balance
FROM Account a
LEFT JOIN LedgerEntry l ON l.account_id = a.account_id AND l.is_reversed = 0
GROUP BY a.account_id, a.holder_name, a.opening_balance
ORDER BY a.account_idAnother way
SELECT a.account_id, a.holder_name,
a.opening_balance
+ (SELECT COALESCE(SUM(amount), 0) FROM LedgerEntry l WHERE l.account_id = a.account_id AND l.entry_type = 'CREDIT' AND l.is_reversed = 0)
- (SELECT COALESCE(SUM(amount), 0) FROM LedgerEntry l WHERE l.account_id = a.account_id AND l.entry_type = 'DEBIT' AND l.is_reversed = 0) AS current_balance
FROM Account a ORDER BY a.account_idAnother way
SELECT a.account_id, a.holder_name, a.opening_balance + COALESCE(n.net, 0) AS current_balance
FROM Account a LEFT JOIN (
SELECT account_id, SUM(IF(entry_type = 'CREDIT', amount, 0)) - SUM(IF(entry_type = 'DEBIT', amount, 0)) AS net
FROM LedgerEntry WHERE is_reversed = 0 GROUP BY account_id
) n ON n.account_id = a.account_id ORDER BY a.account_id← UPI Success Rate by App and Month · Borrowers With Repeatedly Overdue EMIs →