Write a SQL query using window functions to calculate a rolling 30-day claims count by policyholder and rank regions by loss ratio. Use the policy, policyholder, and claims data, treating the 30-day period as the claim date plus the preceding 29 calendar days. Assume loss ratio equals total claim amount divided by total annual premium.
Output
- One row per policyholder and claim date with
policyholder_name, claim_date, rolling_30_day_claims, region, loss_ratio, and region_rank.
- Include only policyholders with claims, order by policyholder name and claim date, and rank regions by descending loss ratio with tied regions sharing a rank.
Output details
- Round
loss_ratio to exactly 4 decimal places.
- Treat missing claim amounts as zero and return
NULL when a region's total annual premium is zero.
- Rank
NULL loss ratios last.
- Include regions represented by policies even when they have no claims, assigning them a zero total claim amount.
- Order by
policyholder_name and claim_date ascending.
