Case Study: Insurance Loss Ratio
Case: Build reporting for written premium, earned premium, and incurred claims by product and month.
This case tests grain control and denominator correctness.
Loss Ratio Contract #
Define explicitly:
- numerator: incurred_amount (incurred claims, not just what has been paid out)
- denominator: earned_premium
- grain: month x product
- null/zero policy: divide by NULLIF(earned_premium, 0)
Sidebar: Paid vs Incurred #
Interviewers love this distinction, so treat it as a contract question, not a detail:
- Paid is cash out the door so far. It lags reality: a claim reported yesterday may have paid 0 even though the company already owes a large amount.
- Incurred = paid + case reserves (the estimate of what the claim will ultimately cost). Reserves = incurred − paid.
- Loss ratios are quoted on incurred claims, because a paid-based ratio looks artificially healthy while claims are young and then deteriorates as payments catch up.
On this dataset the gap is visible: total incurred is 3,620.00 but total paid is only 2,500.00. The remaining 1,120.00 is sitting in reserves on open claims. A paid-based ratio would understate every month with open claims.
A strong answer asks the interviewer "paid or incurred?" before writing SQL, then defaults to incurred if the business does not say otherwise.
Model Output (Self-Check) #
Your month × product loss ratios should reconcile to these totals on the seeded data. Portfolio incurred is 3,620.00 against earned premium of 4,520.82, an ~80% loss ratio, but the monthly shape is the story: a sharp spike early that cools every month as earned premium accrues. February is the worst month (claims land before much premium has earned), with Commercial the heaviest line: policy 1005's two February claims (1,100 + 220 incurred) against ~250 earned give a loss ratio well above 5.0. By April–May the portfolio settles under 1.0. If your numbers don't show "high-and-cooling," check that you used earned (not written) premium and incurred (not paid) claims.
Validation Query #
-- Detect impossible cases where paid claims exceed incurred claims
SELECT
claim_id,
incurred_amount,
paid_amount
FROM ins_claims
WHERE paid_amount > incurred_amount;Scoring Rubric #
- loss-ratio contract correctness (35%) — strong: states incurred / earned, the month x product grain, and the NULLIF guard before writing any SQL
- grain alignment and join safety (30%) — strong: aggregates premium and claims to the same grain before joining, so nothing fans out or double-counts
- validation quality (20%) — strong: runs at least one impossible-case check, like paid_amount > incurred_amount
- explanation clarity (15%) — strong: explains in one sentence why the denominator is earned (not written) and the numerator incurred (not paid)
Two silent metric divergences sink this case: using written instead of earned premium in the denominator, and using paid instead of incurred claims in the numerator. Both make the ratio look better than reality. Do not switch either one unless the business explicitly asks for it, and say out loud which pair you chose.