Join Patient Data Subset | Dataford Interview Questions

Given a physical database diagram with three tables, write a query using inner joins to return a specific subset of patient data.

Return patients with an encounter during 2024 and a diagnosis code of E11.9 or I10. Include only records with matching patient, encounter, and diagnosis rows.

Output

One row per matching patient diagnosis encounter
Columns: patient_id, patient_name, encounter_date, diagnosis_code, and diagnosis_name
Sort by encounter_date, then patient_id, then diagnosis_code

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
full_name	VARCHAR(100)	Patient's full name
medical_record_number	VARCHAR(20)	Medical record number
sex	VARCHAR(20)	Recorded patient sex
insurance_member_id	VARCHAR(30)	Insurance member identifier

encounters

Column	Type	Description
encounter_idPK	INT	Unique encounter identifier
patient_id	INT	Referenced patient identifier
encounter_date	DATE	Date of the encounter
encounter_type	VARCHAR(30)	Type of clinical encounter
department	VARCHAR(50)	Clinical department

diagnoses

Column	Type	Description
diagnosis_idPK	INT	Unique diagnosis record identifier
encounter_id	INT	Referenced encounter identifier
diagnosis_code	VARCHAR(10)	ICD diagnosis code
diagnosis_name	VARCHAR(100)	Diagnosis description

Your question is Join Patient Data Subset. Start with the requirements and the three 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