Most Recent Event Per Patient | Dataford Interview Questions

Write a query to find the most recent clinical event for each patient using window functions.

Use the patients and clinical_events tables. Return only patients with at least one clinical event. If multiple events share the latest timestamp, select the event with the highest event_id.

Output

One row per patient, ordered by patient_id ascending.
Columns: patient_id, event_id, event_type, and event_timestamp.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

clinical_events

Column	Type	Description
event_idPK	INT	Unique clinical event identifier
patient_id	INT	Patient associated with the event
event_type	VARCHAR(50)	Clinical event classification
event_timestamp	TIMESTAMPTZ	Timestamp when the event occurred

Your question is Most Recent Event Per Patient. 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