SQL Join for Latest Clinical Record | Dataford Interview Questions

Write a SQL query to join patient demographic tables with clinical test results, returning the most recent test record per patient.

Use the provided patient demographic and clinical test result tables. Include patients without test results, and resolve multiple tests on the same date by choosing the greatest test ID.

Output

One row per patient, ordered by patient_id.
Include patient_id, first_name, last_name, date_of_birth, test_id, test_name, test_date, result_value, and unit.

Schema

patient_demographics

Column	Type	Description
patient_idPK	INT	Unique patient identifier
first_name	VARCHAR(50)	Patient first name
last_name	VARCHAR(50)	Patient last name
date_of_birth	DATE	Patient date of birth

clinical_test_results

Column	Type	Description
test_idPK	INT	Unique clinical test record identifier
patient_id	INT	Patient associated with the test
test_name	VARCHAR(100)	Name of the clinical test
test_date	TIMESTAMP	Date and time the test was performed
result_value	VARCHAR(100)	Reported test result
unit	VARCHAR(30)	Unit associated with the result

Your question is SQL Join for Latest Clinical Record. 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