Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Klarna
Classify each loan into a delinquency bucket based on its current consecutive missed-payment streak, and roll results up by origination vintage.
Klarna's risk team needs to know whether delinquency is a book-wide rot or one bad cohort — and the lens for that is vintage analysis (grouping loans by origination month, then tracking each vintage's health separately).
Each loan lands in a delinquency bucket from its current consecutive-missed-payment streak: Current (0 missed), 30 DPD (days past due — 1 miss), 60 DPD (2 consecutive), 90+ DPD / Default (3+). The streak logic is the technical core — a gaps-and-islands pattern that groups consecutive misses and keeps only the streak touching the latest due date (a borrower who missed, then paid current again, is Current, not delinquent).
The table then shows bucket mix per vintage with percentages. One bad vintage (February at 50% default while January and March are fully Current) means underwriting, fraud, or collections for that cohort — pull its origination profile before touching book-wide policy. A single recovered loan (missed once, then clean) is equally valuable: dissect the save and systematize it.
loans with loan_id, origination_date, loan_amount, monthly_installment; loan_payments with one row per scheduled installment per loan, including due_date, amount_due, amount_paid, and paid_date (NULL for missed payments).
CREATE TABLE loans (
loan_id INTEGER PRIMARY KEY,
origination_date DATE,
loan_amount NUMERIC(10,2),
monthly_installment NUMERIC(10,2)
);
CREATE TABLE loan_payments (
payment_id INTEGER PRIMARY KEY,
loan_id INTEGER,
due_date DATE,
amount_due NUMERIC(10,2),
amount_paid NUMERIC(10,2),
paid_date DATE,
FOREIGN KEY (loan_id) REFERENCES loans(loan_id)
);Your target
Classify each loan into a delinquency bucket based on its current consecutive missed-payment streak, and roll results up by origination vintage.
Loading the expected output…
WITH payment_flags AS (
SELECT
loan_id,
due_date,
amount_due,
amount_paid,
CASE WHEN amount_paid < amount_due THEN 1 ELSE 0 END AS is_missed,
ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY due_date) AS payment_seq
FROM loan_payments
),
islands AS (
SELECT
*,
payment_seq - ROW_NUMBER() OVER (
PARTITION BY loan_id, is_missed ORDER BY due_date
) AS streak_group
FROM payment_flags
),
streaks AS (
SELECT
loan_id,
is_missed,
streak_group,
COUNT(*) AS streak_length,
MAX(due_date) AS streak_end_date
FROM islands
WHERE is_missed = 1
GROUP BY loan_id, is_missed, streak_group
),
latest_payment AS (
SELECT loan_id, MAX(due_date) AS last_due_date
FROM payment_flags
GROUP BY loan_id
),
current_streak AS (
SELECT s.loan_id, s.streak_length
FROM streaks s
JOIN latest_payment lp ON lp.loan_id = s.loan_id AND lp.last_due_date = s.streak_end_date
),
delinquency AS (
SELECT
l.loan_id,
l.origination_date,
strftime('%Y-%m-01', l.origination_date) AS vintage_month,
COALESCE(cs.streak_length, 0) AS consecutive_missed_payments,
CASE
WHEN COALESCE(cs.streak_length, 0) = 0 THEN 'Current'
WHEN cs.streak_length = 1 THEN '30 DPD'
WHEN cs.streak_length = 2 THEN '60 DPD'
ELSE '90+ DPD / Default'
END AS delinquency_bucket
FROM loans l
LEFT JOIN current_streak cs ON cs.loan_id = l.loan_id
)
SELECT
strftime('%Y-%m', vintage_month) AS vintage_month,
delinquency_bucket,
COUNT(*) AS loan_count,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY vintage_month), 1) AS pct_of_vintage
FROM delinquency
GROUP BY vintage_month, delinquency_bucket
ORDER BY vintage_month, delinquency_bucket;
Step-by-step Solution:
payment_flags marks each installment as missed (is_missed = 1) when amount_paid is less than amount_due, and numbers each loan's payments in chronological order.
The islands CTE applies the classic gaps-and-islands technique: subtracting a row number computed within each (loan_id, is_missed) group from the overall payment sequence number produces a constant streak_group value for every run of consecutive missed (or consecutive paid) payments.
streaks aggregates each missed-payment island to get its length and the due_date it ends on.
current_streak keeps only the streak that ends on each loan's most recent due date — this is what determines the loan's live delinquency status, since an older missed streak that was later paid off should no longer count.
The final query buckets each loan by that current streak length (0 = Current, 1 = 30 DPD, 2 = 60 DPD, 3+ = 90+ DPD/Default) and rolls the results up by origination vintage month with a percentage share per bucket.
Computing the reference output…
The February 2024 vintage is the problem child: 50% sits in 90+ DPD/Default (loan 3 missed three straight after two clean payments) while the January and March vintages are fully Current. Pull Feb-vintage underwriting criteria and put loan 3 into collections immediately — one bad cohort, not a book-wide rot.
What the interviewer asks next
“February's vintage is 50% in default while January and March are fully Current — underwriting failure, fraud ring, or collections gap?”
Strong answer covers: Triangulation: compare Feb originations' profile (ticket size, channel, first-payment behavior) against clean vintages before blaming any single cause — cohort anomalies are evidence, not verdicts.
“Loan 2 paid late, missed twice, then paid current again — restructure the loan or intensify collections?”
Strong answer covers: Cure behavior reads intent: a borrower who resumes paying signals willingness with volatility — restructure into sustainable installments rather than punishing the recovery.
“Loan 4 missed once then fully recovered — what made that cure work, and how do you systematize it?”
Strong answer covers: Studying the save: early contact timing, payment-plan flexibility, or borrower profile — turn one recovery into the early-collections playbook for the whole book.
“Loan 2's trail reads miss–pay–miss–miss–pay while loan 3 reads pay–pay–miss–miss–miss — yet the buckets only see the current streak. What information is the model throwing away?”
Strong answer covers: History-blindness: identical current streaks with opposite trajectories (recovering vs deteriorating) get identical treatment — argue for trajectory-aware buckets or a separate watch-list for volatile payers.
“Loan 5 has a single on-time payment and reads Current — same label as loan 1's four-payment record. Same risk?”
Strong answer covers: Thin-file equivalence: one observation cannot support the confidence the shared label implies — propose seasoning requirements or provisional labels for unscored loans.
“Installments range Rs.2K–Rs.6K but buckets count misses, not rupees — loan 3 (Rs.72K book, defaulting) vs loan 4 (Rs.24K, recovered). Where is the actual money at risk?”
Strong answer covers: Exposure-weighted risk: DPD buckets measure behavior, loss-given-default measures money — multiply the two (delinquent balance × severity) to rank collections effort by expected loss, not count.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.