Latest Lab Result SQL | Dataford Interview Questions

Write a SQL query to find the latest lab result for each patient in a study, partitioned by patient ID and ordered by visit date.

Use the study identified by study_code = 'PMG-ONC-101'. Include enrolled patients without a lab result, and use result_id as the tie-breaker when a patient has multiple results on the same visit date.

Output

One row per enrolled patient, ordered by patient_id ascending.
Columns: patient_id, visit_date, lab_test, and result_value.
Patients without results should have NULL values for the lab result columns.

Schema

studies

Column	Type	Description
study_idPK	INT	Unique study identifier
study_code	VARCHAR(30)	Study code used to identify the study
study_name	VARCHAR(120)	Study name

study_patients

Column	Type	Description
study_patient_idPK	INT	Unique enrollment record identifier
study_id	INT	Referenced study
patient_id	VARCHAR(20)	Patient identifier
enrolled_on	DATE	Study enrollment date

lab_results

Column	Type	Description
result_idPK	INT	Unique lab result identifier
patient_id	VARCHAR(20)	Patient associated with the result
visit_date	DATE	Date of the patient visit
lab_test	VARCHAR(80)	Name of the laboratory test
result_value	VARCHAR(40)	Reported laboratory result value

Your question is Latest Lab Result SQL. 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