Loan Applicants by Credit Score Band

Easy
53.

Loan Applicants by Credit Score Band

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

ColumnType
application_idint
applicant_namevarchar
credit_scoreint
requested_amountint

Primary key: application_id.

One row per application. credit_score is 300–900, or NULL when the bureau has no history for the applicant.

Example

The rows must come back in the order shown.

LoanApplication

application_idapplicant_namecredit_scorerequested_amount
1Aditi Rao812300000
2Farhan Khan750150000
3Neha Joshi749500000
4Dev MehtaNULL75000
5Pooja Bose650200000
6Karan Singh612400000

Output

application_idapplicant_namecredit_band
1Aditi RaoPrime
2Farhan KhanPrime
3Neha JoshiNear Prime
4Dev MehtaNo Bureau Record
5Pooja BoseNear Prime
6Karan SinghSubprime
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 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

ColumnType
application_idint
applicant_namevarchar
credit_scoreint
requested_amountint

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_idapplicant_namecredit_scorerequested_amount
1Aditi Rao812300000
2Farhan Khan750150000
3Neha Joshi749500000
4Dev MehtaNULL75000
5Pooja Bose650200000
6Karan Singh612400000

Output

application_idapplicant_namecredit_band
1Aditi RaoPrime
2Farhan KhanPrime
3Neha JoshiNear Prime
4Dev MehtaNo Bureau Record
5Pooja BoseNear Prime
6Karan SinghSubprime

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_id

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

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