30-Minute Business Case Sprint
Practice Sprint Timer
Start with a first-correct query, then use remaining minutes for validation and cleanup.
This sprint simulates a business KPI round with multi-table insurance data.
Time budget:
- 0-12 min: Drill 1
- 12-30 min: Drill 2
Drill 1: Product-Line Loss Ratio #
Return loss ratio by product line using fanout-safe pre-aggregation.
Required output columns:
- product_line
- earned_premium (rounded to 2 decimals)
- incurred_loss (rounded to 2 decimals)
- loss_ratio
Rounding contract: loss_ratio = incurred_loss / earned_premium, rounded to exactly 4 decimal places with ROUND(..., 4). Use NULLIF in the denominator so a zero-premium line yields NULL instead of an error.
Drill 2: Monthly Portfolio KPI View #
Return monthly premium and claims view.
Required output columns:
- month_start
- written_premium (rounded to 2 decimals)
- earned_premium (rounded to 2 decimals)
- incurred_loss (rounded to 2 decimals)
- claim_count
- loss_ratio
Rounding contract: loss_ratio = incurred_loss / earned_premium, rounded to exactly 4 decimal places with ROUND(..., 4). Months with no earned premium must show NULL loss_ratio (NULLIF denominator); missing premium or claims values default to 0.
Passing score target: >= 14/18. Under time pressure, deliver one validated query before adding refinements.