Current Balance From the Account Ledger

Medium
57.

Current Balance From the Account Ledger

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

ColumnType
account_idint
holder_namevarchar
opening_balanceint

Primary key: account_id.

One row per account; opening_balance is in rupees.

Table: LedgerEntry

ColumnTypeValues
entry_idint
account_idint
entry_typeenumCREDIT, DEBIT
amountint
posted_ondate
is_reversedbool

Primary key: entry_id.

One row per posting; amount is always positive and entry_type gives its direction. account_id always names an account.

Example

The rows must come back in the order shown.

Account

account_idholder_nameopening_balance
1Saanvi Reddy10000
2Arjun Menon0
3Olivia Joshi25000
4Nikhil Bose5000

LedgerEntry

entry_idaccount_identry_typeamountposted_onis_reversed
11CREDIT500002024-07-010
21DEBIT120002024-07-030
32CREDIT80002024-07-040
42DEBIT30002024-07-051
51DEBIT15002024-07-090
64CREDIT200002024-07-101
72DEBIT25002024-07-120

Output

account_idholder_namecurrent_balance
1Saanvi Reddy46500
2Arjun Menon5500
3Olivia Joshi25000
4Nikhil Bose5000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
account_idint
holder_namevarchar
opening_balanceint

Primary key: account_id.

One row per account; opening_balance is in rupees.

Table: LedgerEntry

ColumnType
entry_idint
account_idint
entry_typeenum(CREDIT, DEBIT)
amountint
posted_ondate
is_reversedbool

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_idholder_nameopening_balance
1Saanvi Reddy10000
2Arjun Menon0
3Olivia Joshi25000
4Nikhil Bose5000

LedgerEntry

entry_idaccount_identry_typeamountposted_onis_reversed
11CREDIT500002024-07-010
21DEBIT120002024-07-030
32CREDIT80002024-07-040
42DEBIT30002024-07-051
51DEBIT15002024-07-090
64CREDIT200002024-07-101
72DEBIT25002024-07-120

Output

account_idholder_namecurrent_balance
1Saanvi Reddy46500
2Arjun Menon5500
3Olivia Joshi25000
4Nikhil Bose5000

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_id

Another 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_id

Another 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 →