Interview Question Bank · CASE buckets · NULL handling · custom sort
1. Loan delinquency buckets
EasyBankingQuestion #97
Concept: CASE buckets · NULL handling · custom sortshow
CASE WHEN ... THEN ... END turns a number into a label. The WHENs are checked top to bottom and the first true one wins, so after the first bucket each WHEN only needs its upper bound. A NULL fails every comparison and falls through to ELSE, so decide where NULLs belong and test for them explicitly.
Example (different data):
SELECT CASE
WHEN score IS NULL THEN 'unscored'
WHEN score < 580 THEN 'poor'
WHEN score < 670 THEN 'fair'
ELSE 'good'
END AS band,
COUNT(*) AS applicants
FROM credit_checks
GROUP BY band;Your task
The collections team at Copperleaf Bank reviews its loan book every Monday, grouped by how late the borrowers are.
Put each loan in fin_loans in a bucket by days_past_due: 'current' (0, or NULL because no payment is due yet), '1-30', '31-60', '61-90' and '91+' (each range includes both ends). Return bucket, loans (number of loans) and total_balance (sum of balance) for each bucket, sorted from 'current' through '1-30', '31-60', '61-90' to '91+'.
Starting Postgres in your browser…
Checking your access…