Loan Book Asset Classification by Branch

Medium
64.

Loan Book Asset Classification by Branch

RBI asks banks to classify loans by days past due (dpd): 0 is standard, 1 to 90 is special mention (SMA), and more than 90 is a non-performing asset (NPA). The credit-risk team needs each branch's book split this way.

For every branch, return branch_name, total_loans, standard_amount, sma_amount and npa_amount (the summed outstanding of the loans in each class, 0 when a class is empty) and npa_loans (how many loans are NPA). Order the rows by branch_name.

Tables

Table: LoanBook

ColumnTypeValues
loan_idint
branch_namevarchar
productenumHOME, PERSONAL, GOLD, VEHICLE
outstandingint
dpdint

Primary key: loan_id.

One row per live loan; outstanding is the principal still owed in rupees, dpd the days since the oldest unpaid instalment fell due (0 if none).

Example

The rows must come back in the order shown.

LoanBook

loan_idbranch_nameproductoutstandingdpd
1Koregaon ParkHOME42000000
2Koregaon ParkPERSONAL31000045
3Koregaon ParkGOLD9000091
4Salt LakeVEHICLE64000090
5Salt LakePERSONAL1500000
6Banjara HillsHOME2800000120
7Salt LakeGOLD750001

Output

branch_nametotal_loansstandard_amountsma_amountnpa_amountnpa_loans
Banjara Hills10028000001
Koregaon Park34200000310000900001
Salt Lake315000071500000
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.

Loan Book Asset Classification by Branch — SQL Problem

RBI asks banks to classify loans by days past due (dpd): 0 is standard, 1 to 90 is special mention (SMA), and more than 90 is a non-performing asset (NPA).

  • Difficulty: Medium
  • Topics: Conditional Logic, Aggregation
  • Dialect: MySQL
  • Problem: #64

Problem statement

RBI asks banks to classify loans by days past due (dpd): 0 is standard, 1 to 90 is special mention (SMA), and more than 90 is a non-performing asset (NPA). The credit-risk team needs each branch's book split this way.

For every branch, return branch_name, total_loans, standard_amount, sma_amount and npa_amount (the summed outstanding of the loans in each class, 0 when a class is empty) and npa_loans (how many loans are NPA). Order the rows by branch_name.

Tables

Table: LoanBook

ColumnType
loan_idint
branch_namevarchar
productenum(HOME, PERSONAL, GOLD, VEHICLE)
outstandingint
dpdint

Primary key: loan_id.

One row per live loan; outstanding is the principal still owed in rupees, dpd the days since the oldest unpaid instalment fell due (0 if none).

Examples

Example 1

LoanBook

loan_idbranch_nameproductoutstandingdpd
1Koregaon ParkHOME42000000
2Koregaon ParkPERSONAL31000045
3Koregaon ParkGOLD9000091
4Salt LakeVEHICLE64000090
5Salt LakePERSONAL1500000
6Banjara HillsHOME2800000120
7Salt LakeGOLD750001

Output

branch_nametotal_loansstandard_amountsma_amountnpa_amountnpa_loans
Banjara Hills10028000001
Koregaon Park34200000310000900001
Salt Lake315000071500000

How to solve Loan Book Asset Classification by Branch

This is conditional aggregation, the SQL way of pivoting a classification into columns. Group by branch; then every class column is a SUM over a CASE that yields the loan's outstanding amount when the loan is in that class and 0 otherwise. Because the ELSE is 0, a branch with no NPA gets npa_amount = 0 rather than NULL, and npa_loans is a SUM of 0/1 flags.

The boundaries are where the logic is tested: dpd = 0 is standard, BETWEEN 1 AND 90 is SMA (inclusive at both ends, so 90 is still SMA), and > 90 is NPA. Writing the three tests so they cover every value exactly once is the point; the alternative labels each loan once in a derived table (CASE WHEN dpd > 90 … WHEN dpd > 0 …) and then counts by label, which makes overlaps impossible.

The query is a single pass over the book with one group per branch — the same shape a regulatory return uses, because it stays one scan however many class columns are added.

Reference solution (MySQL)

SELECT branch_name,
       COUNT(*) AS total_loans,
       SUM(CASE WHEN dpd = 0 THEN outstanding ELSE 0 END) AS standard_amount,
       SUM(CASE WHEN dpd BETWEEN 1 AND 90 THEN outstanding ELSE 0 END) AS sma_amount,
       SUM(CASE WHEN dpd > 90 THEN outstanding ELSE 0 END) AS npa_amount,
       SUM(CASE WHEN dpd > 90 THEN 1 ELSE 0 END) AS npa_loans
FROM LoanBook
GROUP BY branch_name
ORDER BY branch_name

Another way

SELECT branch_name, COUNT(*) AS total_loans,
  SUM(IF(cls = 'STD', outstanding, 0)) AS standard_amount,
  SUM(IF(cls = 'SMA', outstanding, 0)) AS sma_amount,
  SUM(IF(cls = 'NPA', outstanding, 0)) AS npa_amount,
  COUNT(CASE WHEN cls = 'NPA' THEN 1 END) AS npa_loans
FROM (SELECT branch_name, outstanding, CASE WHEN dpd > 90 THEN 'NPA' WHEN dpd > 0 THEN 'SMA' ELSE 'STD' END AS cls FROM LoanBook) t
GROUP BY branch_name ORDER BY branch_name

← Inoperative Accounts Under the Two-Year Rule · Running Wallet Balance After Every Transaction →