Active Deposits Held at Each Branch

Easy
50.

Active Deposits Held at Each Branch

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

ColumnType
branch_idint
branch_namevarchar
cityvarchar

Primary key: branch_id.

One row per branch.

Table: Account

ColumnTypeValues
account_idint
branch_idint
account_typeenumSAVINGS, CURRENT
balanceint
statusenumACTIVE, CLOSED

Primary key: account_id.

One row per account; branch_id always names a row of Branch. balance is in rupees.

Example

The rows must come back in the order shown.

Branch

branch_idbranch_namecity
1MG RoadBengaluru
2Andheri WestMumbai
3Salt LakeKolkata
4T NagarChennai

Account

account_idbranch_idaccount_typebalancestatus
1011SAVINGS45000ACTIVE
1021CURRENT230000ACTIVE
1032SAVINGS180000ACTIVE
1042SAVINGS95000ACTIVE
1053SAVINGS60000CLOSED
1064CURRENT275000ACTIVE
1071SAVINGS0CLOSED

Output

branch_idbranch_nameactive_accountstotal_balance
1MG Road2275000
2Andheri West2275000
4T Nagar1275000
3Salt Lake00
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.

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

ColumnType
branch_idint
branch_namevarchar
cityvarchar

Primary key: branch_id.

One row per branch.

Table: Account

ColumnType
account_idint
branch_idint
account_typeenum(SAVINGS, CURRENT)
balanceint
statusenum(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_idbranch_namecity
1MG RoadBengaluru
2Andheri WestMumbai
3Salt LakeKolkata
4T NagarChennai

Account

account_idbranch_idaccount_typebalancestatus
1011SAVINGS45000ACTIVE
1021CURRENT230000ACTIVE
1032SAVINGS180000ACTIVE
1042SAVINGS95000ACTIVE
1053SAVINGS60000CLOSED
1064CURRENT275000ACTIVE
1071SAVINGS0CLOSED

Output

branch_idbranch_nameactive_accountstotal_balance
1MG Road2275000
2Andheri West2275000
4T Nagar1275000
3Salt Lake00

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_id

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

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