Find Duplicate Patient Records | Dataford Interview Questions

Write a query using window functions such as ROW_NUMBER() or DENSE_RANK() to identify duplicate patient records in a clinical dataset.

Use matching first name, last name, date of birth, and phone number to define duplicates. Return records after the earliest record in each duplicate group.

Output

One row per duplicate record, including its source and duplicate position.
Include patient_record_id, source_name, patient identifying fields, duplicate_sequence, and duplicate_count.
Order by patient name, date of birth, duplicate sequence, and record ID.

Schema

patient_records

Column	Type	Description
patient_record_idPK	INT	Unique clinical patient record identifier
source_id	INT	Originating data source identifier
first_name	VARCHAR(100)	Patient first name
last_name	VARCHAR(100)	Patient last name
date_of_birth	DATE	Patient date of birth
phone	VARCHAR(25)	Patient telephone number
created_at	TIMESTAMP	Timestamp when the source record was created

data_sources

Column	Type	Description
source_idPK	INT	Unique source identifier
source_name	VARCHAR(100)	Name of the clinical data source

Your question is Find Duplicate Patient 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