SQL for Unique Patient Records | Dataford Interview Questions

Write a SQL query to extract unique patient records from a clinical database.

Use the patients and clinical_records tables. Return only patients with at least one clinical record, selecting the most recent record for each patient. If timestamps tie, use the larger record_id as the tie-breaker.

Output

One row per patient with a clinical record
Columns: patient_id, full_name, recorded_at, diagnosis, record_status
Sort by patient_id in ascending order

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
full_name	VARCHAR(100)	Patient's full name
date_of_birth	DATE	Patient date of birth
sex	VARCHAR(20)	Recorded patient sex

clinical_records

Column	Type	Description
record_idPK	INT	Unique clinical record identifier
patient_id	INT	Patient associated with the clinical record
recorded_at	TIMESTAMP	Date and time the record was created
diagnosis	VARCHAR(150)	Diagnosis documented in the record
record_status	VARCHAR(30)	Current status of the clinical record

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