SQL Joins and Aggregations | Dataford Interview Questions

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.

Requirements

Use a LEFT JOIN from policies to claim_events, retaining policy segments with no qualifying 2025 claims.
Group results by state_code and policy_type.
Return distinct policy count, 2025 claim count, and total incurred amount. Treat missing incurred amounts and segments without claims as zero.
Sort by state, with NULL states last, then by policy type.

Schema

policies

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

claim_events

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

Your question is SQL Joins and Aggregations. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem