Deduplicate Claim Records | Dataford Interview Questions

Write a query to identify duplicate claim entries at Sedgwick based on a combination of claimant ID, date of injury, and claim type, keeping only the most recent record.

Use the sedgwick_claims table. Treat records with the same three identifying fields as duplicates, and use recorded_at to determine recency.

Output

Return one row per duplicate combination, containing the retained claim record and duplicate_count.
Include claim_id, claimant_id, date_of_injury, claim_type, recorded_at, and duplicate_count.
Order by claimant_id, date_of_injury, and claim_type.

Schema

sedgwick_claims

Column	Type	Description
claim_idPK	INT	Unique identifier for the claim record
claimant_id	INT	Identifier of the claimant
date_of_injury	DATE	Date the injury occurred
claim_type	VARCHAR(50)	Classification of the claim
recorded_at	TIMESTAMP	Timestamp when the claim record was recorded or updated

Your question is Deduplicate Claim Records. Start with the requirements and the one table 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