Find Duplicate Claims Records | Dataford Interview Questions

Write a SQL query to find duplicate claims records for Plymouth Rock Assurance.

Treat claims as duplicates when policy_number, claimant_id, loss_date, and claim_amount match. Include claims with a NULL loss_date when the other duplicate criteria match.

Output

One row per claim belonging to a duplicate group.
Columns: claim_number, policy_number, claimant_name, loss_date, claim_amount, and duplicate_count.
Exclude claims without a duplicate, order by policy_number, loss_date with NULL values last, then claim_number.

Schema

claims

Column	Type	Description
claim_numberPK	VARCHAR(20)	Unique claim identifier
policy_number	VARCHAR(20)	Insurance policy identifier
claimant_id	INT	Claimant associated with the claim
loss_date	DATE	Date on which the covered loss occurred
claim_amount	NUMERIC(12,2)	Reported claim amount
claim_status	VARCHAR(20)	Current claim processing status

claimants

Column	Type	Description
claimant_idPK	INT	Unique claimant identifier
claimant_name	VARCHAR(100)	Full name of the claimant

Your question is Find Duplicate Claims Records. 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