Rolling 30-Day Admissions Query | Dataford Interview Questions

Write a SQL query to calculate a rolling 30-day average of patient admissions.

Use the dated records in patient_admissions and include calendar days with no admissions in the average. Return results for the full date range represented by the valid admission dates.

Output

One row per calendar date in the valid admission date range.
Columns: admission_date, daily_admissions, and rolling_30_day_average.
Include zero-admission days, exclude records with a NULL admission date, and order by admission_date ascending.

Schema

patient_admissions

Column	Type	Description
admission_idPK	INT	Unique identifier for the admission
patient_id	INT	Identifier of the admitted patient
admission_date	DATE	Date on which the admission occurred

Your question is Rolling 30-Day Admissions Query. Start with the requirements and the one table 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