Loan Applicants by Credit Score Band — SQL Problem
The personal-loan desk prices every application by the applicant's bureau credit score.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #53
Problem statement
The personal-loan desk prices every application by the applicant's bureau credit score. A score of 750 or above is Prime, 650 to 749 is Near Prime, anything below 650 is Subprime, and an applicant with no bureau record (score NULL) is No Bureau Record.
Return application_id, applicant_name and credit_band for every application, ordered by application_id.
Tables
Table: LoanApplication
| Column | Type |
|---|---|
| application_id | int |
| applicant_name | varchar |
| credit_score | int |
| requested_amount | int |
Primary key: application_id.
One row per application. credit_score is 300–900, or NULL when the bureau has no history for the applicant.
Examples
Example 1
LoanApplication
| application_id | applicant_name | credit_score | requested_amount |
|---|---|---|---|
| 1 | Aditi Rao | 812 | 300000 |
| 2 | Farhan Khan | 750 | 150000 |
| 3 | Neha Joshi | 749 | 500000 |
| 4 | Dev Mehta | NULL | 75000 |
| 5 | Pooja Bose | 650 | 200000 |
| 6 | Karan Singh | 612 | 400000 |
Output
| application_id | applicant_name | credit_band |
|---|---|---|
| 1 | Aditi Rao | Prime |
| 2 | Farhan Khan | Prime |
| 3 | Neha Joshi | Near Prime |
| 4 | Dev Mehta | No Bureau Record |
| 5 | Pooja Bose | Near Prime |
| 6 | Karan Singh | Subprime |
How to solve Loan Applicants by Credit Score Band
Each row maps to one label, which is what a searched CASE expression is for. The branches are tried in order, so testing >= 750 first and then >= 650 gives the bands without writing both ends of each range: a score that reaches the second branch is already known to be below 750.
The NULL score needs care. Every comparison with NULL is unknown, so no numeric branch matches and the CASE would fall through to ELSE — labelling a missing score Subprime, a real mistake in lending. Testing credit_score IS NULL first avoids it. The second alternative takes the other route: it leaves out ELSE, so a NULL score yields NULL from the CASE, and COALESCE turns that into No Bureau Record.
Nested IF calls work the same way in MySQL. Boundary values (exactly 750, 650 and 649) are where such queries usually go wrong, so check them first. The query is one scan; the sort is on the primary key.
Reference solution (MySQL)
SELECT application_id, applicant_name,
CASE
WHEN credit_score IS NULL THEN 'No Bureau Record'
WHEN credit_score >= 750 THEN 'Prime'
WHEN credit_score >= 650 THEN 'Near Prime'
ELSE 'Subprime'
END AS credit_band
FROM LoanApplication
ORDER BY application_idAnother way
SELECT application_id, applicant_name, IF(credit_score IS NULL, 'No Bureau Record', IF(credit_score >= 750, 'Prime', IF(credit_score >= 650, 'Near Prime', 'Subprime'))) AS credit_band FROM LoanApplication ORDER BY application_idAnother way
SELECT application_id, applicant_name, COALESCE(CASE WHEN credit_score < 650 THEN 'Subprime' WHEN credit_score BETWEEN 650 AND 749 THEN 'Near Prime' WHEN credit_score > 749 THEN 'Prime' END, 'No Bureau Record') AS credit_band FROM LoanApplication ORDER BY application_id← Fixed Deposits Maturing in March 2025 · Customers Holding Both Savings and Current Accounts →