Running Average Over 30 Days | Dataford Interview Questions

Your question is Running Average Over 30 Days. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

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