Window Functions for Latest Patient Event | Dataford Interview Questions

Write a query using window functions to identify the most recent medical event for each patient in a longitudinal dataset at Garner health.

Use the patients and medical_events tables. Include patients without recorded events, and treat a missing event date as older than any non-null date.

Output

One row per patient, ordered by patient_id.
Columns: patient_id, patient_name, event_id, event_type, and event_date.
For equal event dates, select the larger event_id; use NULLs for patients without an event.

Schema

patients

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

medical_events

Column	Type	Description
event_idPK	INT	Unique medical event identifier
patient_id	INT	Patient associated with the event
event_type	VARCHAR(50)	Category of medical event
event_date	DATE	Date on which the medical event occurred

Your question is Window Functions for Latest Patient Event. 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