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
| Column | Type |
|---|---|
| loan_id | int |
| branch_name | varchar |
| product | enum(HOME, PERSONAL, GOLD, VEHICLE) |
| outstanding | int |
| dpd | int |
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_id | branch_name | product | outstanding | dpd |
|---|---|---|---|---|
| 1 | Koregaon Park | HOME | 4200000 | 0 |
| 2 | Koregaon Park | PERSONAL | 310000 | 45 |
| 3 | Koregaon Park | GOLD | 90000 | 91 |
| 4 | Salt Lake | VEHICLE | 640000 | 90 |
| 5 | Salt Lake | PERSONAL | 150000 | 0 |
| 6 | Banjara Hills | HOME | 2800000 | 120 |
| 7 | Salt Lake | GOLD | 75000 | 1 |
Output
| branch_name | total_loans | standard_amount | sma_amount | npa_amount | npa_loans |
|---|---|---|---|---|---|
| Banjara Hills | 1 | 0 | 0 | 2800000 | 1 |
| Koregaon Park | 3 | 4200000 | 310000 | 90000 | 1 |
| Salt Lake | 3 | 150000 | 715000 | 0 | 0 |
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_nameAnother 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 →