Active Deposits Held at Each Branch — SQL Problem
The regional head wants each branch's deposit book: how many active accounts it holds and their combined balance. Closed accounts no longer count.
- Difficulty: Easy
- Topics: Aggregation, Joins
- Dialect: MySQL
- Problem: #50
Problem statement
The regional head wants each branch's deposit book: how many active accounts it holds and their combined balance. Closed accounts no longer count. A branch with no active account still appears, with 0 accounts and a total of 0.
Return branch_id, branch_name, active_accounts and total_balance, ordered by total_balance descending, then by branch_id ascending.
Tables
Table: Branch
| Column | Type |
|---|---|
| branch_id | int |
| branch_name | varchar |
| city | varchar |
Primary key: branch_id.
One row per branch.
Table: Account
| Column | Type |
|---|---|
| account_id | int |
| branch_id | int |
| account_type | enum(SAVINGS, CURRENT) |
| balance | int |
| status | enum(ACTIVE, CLOSED) |
Primary key: account_id.
One row per account; branch_id always names a row of Branch. balance is in rupees.
Examples
Example 1
Branch
| branch_id | branch_name | city |
|---|---|---|
| 1 | MG Road | Bengaluru |
| 2 | Andheri West | Mumbai |
| 3 | Salt Lake | Kolkata |
| 4 | T Nagar | Chennai |
Account
| account_id | branch_id | account_type | balance | status |
|---|---|---|---|---|
| 101 | 1 | SAVINGS | 45000 | ACTIVE |
| 102 | 1 | CURRENT | 230000 | ACTIVE |
| 103 | 2 | SAVINGS | 180000 | ACTIVE |
| 104 | 2 | SAVINGS | 95000 | ACTIVE |
| 105 | 3 | SAVINGS | 60000 | CLOSED |
| 106 | 4 | CURRENT | 275000 | ACTIVE |
| 107 | 1 | SAVINGS | 0 | CLOSED |
Output
| branch_id | branch_name | active_accounts | total_balance |
|---|---|---|---|
| 1 | MG Road | 2 | 275000 |
| 2 | Andheri West | 2 | 275000 |
| 4 | T Nagar | 1 | 275000 |
| 3 | Salt Lake | 0 | 0 |
How to solve Active Deposits Held at Each Branch
Group accounts by branch and aggregate, but start from Branch with a LEFT JOIN so a branch whose accounts are all closed (or that has none) still produces a row. The status test belongs in the ON clause: in WHERE it would discard the NULL row of an unmatched branch and the branch would disappear.
For a branch with no active account, the joined account columns are NULL. COUNT(a.account_id) counts only non-NULL values and gives 0, while SUM(a.balance) over only NULLs is NULL — COALESCE(…, 0) turns it into the 0 the statement asks for.
Conditional aggregation (SUM(CASE WHEN status = 'ACTIVE' …)) over a plain LEFT JOIN gets there too, and the CASE's ELSE 0 handles the empty branch on its own; two correlated subqueries per branch are the third option. The tie on total balance is broken by branch_id, so the order is fixed. The cost is one pass over accounts plus a sort of the branches.
Reference solution (MySQL)
SELECT b.branch_id, b.branch_name,
COUNT(a.account_id) AS active_accounts,
COALESCE(SUM(a.balance), 0) AS total_balance
FROM Branch b
LEFT JOIN Account a ON a.branch_id = b.branch_id AND a.status = 'ACTIVE'
GROUP BY b.branch_id, b.branch_name
ORDER BY total_balance DESC, b.branch_idAnother way
SELECT b.branch_id, b.branch_name,
(SELECT COUNT(*) FROM Account a WHERE a.branch_id = b.branch_id AND a.status = 'ACTIVE') AS active_accounts,
(SELECT COALESCE(SUM(balance), 0) FROM Account a WHERE a.branch_id = b.branch_id AND a.status = 'ACTIVE') AS total_balance
FROM Branch b ORDER BY total_balance DESC, b.branch_idAnother way
SELECT b.branch_id, b.branch_name,
SUM(CASE WHEN a.status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_accounts,
SUM(CASE WHEN a.status = 'ACTIVE' THEN a.balance ELSE 0 END) AS total_balance
FROM Branch b LEFT JOIN Account a ON a.branch_id = b.branch_id
GROUP BY b.branch_id, b.branch_name ORDER BY total_balance DESC, b.branch_id← Customers Without a Verified KYC Document · Masked Card Numbers for Card Statements →