Your question is SQL Joins and Aggregations. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Verisk Analytics uses ISO ClaimSearch data to support insurer portfolio analysis. Write a PostgreSQL query that joins policy records to claim events and summarizes 2025 claims by policy segment.
LEFT JOIN from policies to claim_events, retaining policy segments with no qualifying 2025 claims.state_code and policy_type.NULL states last, then by policy type.| Column | Type | Description |
|---|---|---|
| policy_idPK | INT | Unique policy identifier |
| state_code | VARCHAR(2) | Two-letter policy state |
| policy_type | VARCHAR(30) | Insurance line of business |
| Column | Type | Description |
|---|---|---|
| claim_idPK | INT | Unique claim identifier |
| policy_id | INT | Policy associated with the claim |
| reported_date | DATE | Date the claim was reported |
| incurred_amount | DECIMAL(12,2) | Current incurred loss amount |
| status | VARCHAR(20) | Claim processing status |