Drill 5 of 10 in Banking questions
0

Interview Question Bank · CASE · NULLIF · NULL-safe ratios

5. Credit utilization buckets

EasyBankingQuestion #263

Concept: CASE · NULLIF · NULL-safe ratiosshow

A ratio needs two guards: a NULL denominator makes the result NULL, and a zero denominator raises an error. NULLIF(x, 0) turns a zero into NULL, so both cases end up as NULL and you can label them with one IS NULL branch. CASE checks its branches top to bottom and stops at the first true one, so put the NULL check first and walk the thresholds in order.

Example (different data):

SELECT room,
  CASE
    WHEN capacity IS NULL OR capacity = 0 THEN 'unknown'
    WHEN 100.0 * booked / capacity < 50 THEN 'quiet'
    ELSE 'busy'
  END AS load
FROM meeting_rooms;

Your task

Larkspur Bank's card team sorts every credit card into a utilization band (balance as a share of the credit limit). Some cards have no limit on file yet, and frozen cards carry a limit of 0.

Return card_id, holder, utilization_pct (100 * balance / credit_limit, rounded to 1 decimal; NULL when the limit is NULL or 0) and bucket: 'no limit' when utilization_pct is NULL, 'low' under 30, 'moderate' from 30 to under 70, 'high' from 70 to under 100, and 'over limit' at 100 or more. Sort by card_id.

Starting Postgres in your browser…

Checking your access…