Insurance Case Study Capstone
Case brief:
You are supporting an underwriting analytics review. Build a reliable KPI pack and then produce a policy watchlist for investigation.
Use fanout-safe query patterns and explain metric grain decisions.
Task 1: Segment x Product-Line KPI Pack #
Return one row per product_line x segment for active policies with:
active_policiesearned_premiumincurred_claimsloss_ratio
Loading SQL editor...
Task 2: Underwriting Watchlist (Top 5 Policies) #
Build a top-5 watchlist of active policies with highest policy-level loss ratio.
Return:
policy_idcustomer_nameproduct_lineearned_premiumincurred_claimsloss_ratioopen_claim_count
Definition: an "open claim" is any claim not yet closed, with a claim_status of 'Open' or 'Reported'.
Loading SQL editor...
Debrief #
Explain in one or two sentences for each task:
- why your chosen grain was correct
- how you prevented fanout errors
- why your denominator policy for loss ratio is defensible
Warning
In production insurance analytics, loss ratio definitions vary by accounting policy and period alignment. Keep metric contracts explicit.