SQL for Duplicate Patient Records | Dataford Interview Questions

Write a SQL query to find duplicate patient records across two separate clinical trial databases.

Treat records as duplicates when first name, last name, and date of birth match after trimming whitespace and ignoring letter case. Return each matching record pair, including the source patient IDs and email addresses.

Output

One row per matching patient pair
Columns: trial_a_patient_id, trial_b_patient_id, normalized_first_name, normalized_last_name, date_of_birth, trial_a_email, trial_b_email
Exclude records missing required name or date-of-birth values
Order by normalized last name, normalized first name, date of birth, and patient IDs

Schema

trial_a_patients

Column	Type	Description
patient_idPK	INT	Patient identifier in clinical trial database A
first_name	VARCHAR(100)	Patient first name
last_name	VARCHAR(100)	Patient last name
date_of_birth	DATE	Patient date of birth
email	VARCHAR(255)	Patient email address

trial_b_patients

Column	Type	Description
patient_idPK	INT	Patient identifier in clinical trial database B
first_name	VARCHAR(100)	Patient first name
last_name	VARCHAR(100)	Patient last name
date_of_birth	DATE	Patient date of birth
email	VARCHAR(255)	Patient email address

Your question is SQL for 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