SQL Deduplication for Longitudinal Data | Dataford Interview Questions

Write a SQL query to identify and remove duplicate patient records while preserving the integrity of longitudinal study data.

The canonical record is the record with the lowest patient_id among patients sharing the same identifying details. Reassign all study visits from duplicate records before removing those records.

Output

One row per removed duplicate, with removed_patient_id and canonical_patient_id.
Exclude canonical and non-duplicate patient records.
Order by removed_patient_id ascending.

Schema

patient_records

Column	Type	Description
patient_idPK	INTEGER	Unique patient record identifier
first_name	VARCHAR(80)	Patient first name
last_name	VARCHAR(80)	Patient last name
date_of_birth	DATE	Patient date of birth
site_code	VARCHAR(20)	Clinical study site identifier

study_visits

Column	Type	Description
visit_idPK	INTEGER	Unique study visit identifier
patient_id	INTEGER	Referenced patient record, nullable for an unmatched imported visit
visit_date	DATE	Date of the study visit
visit_type	VARCHAR(40)	Study visit classification

Your question is SQL Deduplication for Longitudinal Data. 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