Running Average Over 30 Days | Dataford Interview Questions

ResMed's therapy analytics team wants to monitor patient adherence for ResMed AirSense 11 users. Write a PostgreSQL query that calculates each patient's running average daily usage over the previous 30 calendar days, including the current usage date.

Requirements

Include only patients using ResMed AirSense 11.
Aggregate multiple usage records for the same patient and date before applying the window function.
Calculate a 30-day running average partitioned by patient and ordered chronologically.
Return the patient code, usage date, daily usage, and running average, ordered by patient and date. Null usage values should be ignored by AVG.

The window covers the current usage date and the 29 calendar days before it; dates with no usage rows are simply absent from the window (the average is over the dates that have data).

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_code	VARCHAR(20)	De-identified patient code
device_model	VARCHAR(50)	ResMed therapy device model

patient_usage

Column	Type	Description
usage_idPK	INT	Unique usage record identifier
patient_id	INT	References patients.patient_id
usage_date	DATE	Date of therapy usage
usage_hours	NUMERIC(5,2)	Therapy usage hours recorded for the date

Your question is Running Average Over 30 Days. 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