Claims Frequency SQL Query | Dataford Interview Questions

Write a SQL query to identify policyholders who have filed more than two claims within a six-month period at Travelers. Treat each claim filing date as the end of an inclusive six-month period, and evaluate claims across all policies owned by the same policyholder.

Output

One row per qualifying policyholder, including policyholder_id, policyholder_name, claims_in_six_month_period, period_start, and period_end.
Include only periods containing more than two claims.
Order by claims_in_six_month_period descending, then policyholder_id ascending.

Schema

policyholders

Column	Type	Description
policyholder_idPK	INT	Unique policyholder identifier
policyholder_name	VARCHAR(100)	Policyholder's full name

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
policy_number	VARCHAR(30)	Business-facing policy number
policyholder_id	INT	Policy owner, referencing policyholders

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policy_id	INT	Policy associated with the claim
filed_date	DATE	Date the claim was filed

Your question is Claims Frequency SQL Query. Start with the requirements and the three 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