Use a self-join to find duplicate records sharing the same natural key
Our registration team suspects we have duplicate patient records — the same person registered twice with different IDs. Find patients who share the same first name, last name, and date of birth but have different patient_ids. Show both IDs and the shared name. This is critical for the CMS review.
Each hint you reveal reduces the XP you can earn. Try the query first.
You need to compare patient rows to each other within the same table. How do you join a table to itself?
Match rows on the natural key (name + DOB) but require the IDs differ. A `<` comparison avoids listing the same pair twice
`FROM dim_patients a JOIN dim_patients b ON a.first_name = ___ AND a.last_name = ___ AND a.date_of_birth = ___ WHERE a.patient_id ___ b.patient_id`