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.
