Claims in Six-Month Window | Dataford Interview Questions

How would you write a SQL query to identify policyholders who have had more than two claims within a six-month window at Aspire General Insurance?

Use the policyholders and claims tables. Treat the six-month boundary as inclusive and return the earliest qualifying window for each policyholder.

Output

One row per qualifying policyholder, with policyholder_id, policyholder_name, window_start, and claim_count.
Include only policyholders with more than two claims in the window.
Order by policyholder_id, then window_start.

Schema

policyholders

Column	Type	Description
policyholder_idPK	INT	Unique policyholder identifier
policyholder_name	VARCHAR(100)	Full name of the policyholder
policy_number	VARCHAR(30)	Insurance policy reference
email	VARCHAR(150)	Policyholder email address

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policyholder_id	INT	Policyholder associated with the claim
claim_date	DATE	Date the claim was filed
claim_status	VARCHAR(30)	Current claim processing status

Your question is Claims in Six-Month Window. 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