Rolling Patient Metrics with SQL Windows | Dataford Interview Questions

How would you use SQL window functions to calculate rolling patient-level metrics over time?

Use the provided patient and measurement data. Return response score observations for active patients, calculating the average of the current and two preceding measurements for each patient. Null measurements should follow PostgreSQL aggregate behavior.

Output

One row per included response score measurement.
Columns: patient_id, patient_name, measurement_date, metric_value, and rolling_3_measurement_avg.
Include active patients only, order by patient_id, measurement_date, and measurement_id.

Schema

patients

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

patient_measurements

Column	Type	Description
measurement_idPK	INT	Unique measurement identifier
patient_id	INT	Referenced patient
measurement_date	DATE	Date the measurement was recorded
metric_name	VARCHAR(50)	Name of the recorded metric
metric_value	NUMERIC(8,2)	Numeric metric value

Your question is Rolling Patient Metrics with SQL Windows. 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