Detect and Clean Duplicate Claims | Dataford Interview Questions

Write a SQL query to identify duplicate records in a healthcare claims table and explain how you would clean them.

For this exercise, treat claims with the same business fields as duplicates and retain the earliest ingested record. Explain how you would review, archive, and remove later duplicates safely.

Output

One row per duplicate claim that should be removed.
Columns: duplicate_claim_id, claim_number, duplicate_group_count, and keep_claim_id.
Sort by claim_number, then duplicate_claim_id ascending.

Schema

healthcare_claims

Column	Type	Description
claim_idPK	INT	Unique physical claim record identifier
claim_number	VARCHAR(20)	Business claim number
member_id	VARCHAR(20)	Covered member identifier
provider_id	VARCHAR(20)	Billing provider identifier
service_date	DATE	Date services were provided
procedure_code	VARCHAR(10)	Healthcare procedure code
billed_amount	DECIMAL(10,2)	Amount billed for the claim line
ingestion_timestamp	TIMESTAMP	Timestamp when the record entered the claims table

Your question is Detect and Clean Duplicate Claims. 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