Drill 1 of 4 in Banking questions
0

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…