Detect Duplicate Medicaid Members | Dataford Interview Questions

Write a SQL query to identify duplicate patient records in a Medicaid database where the first name, last name, and date of birth match, but the member IDs differ.

Use the patients table. Exclude incomplete identity values and avoid returning the same member pair twice.

Output

One row per duplicate member-ID pair.
Return first_name, last_name, date_of_birth, member_id_1, and member_id_2.
Order by name, date of birth, and both member IDs.

Schema

patients

Column	Type	Description
patient_record_idPK	INTEGER	Unique row identifier for the patient record
member_id	VARCHAR(20)	Medicaid member identifier
first_name	VARCHAR(100)	Patient first name
last_name	VARCHAR(100)	Patient last name
date_of_birth	DATE	Patient date of birth

Your question is Detect Duplicate Medicaid Members. 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