Rolling Six-Month Enrollment SQL | Dataford Interview Questions

Write a query using SQL window functions to calculate the rolling average of patient enrollment over the last six months.

Assume enrollment is recorded monthly for each study. Treat a NULL enrollment value as zero, and use the latest enrollment month in the data as the endpoint.

Output

One row per study and enrollment month in the latest six-month period.
Columns: study_name, enrollment_month, monthly_enrollment, and six_month_rolling_avg.
Order by study_name, then enrollment_month ascending.

Schema

patient_enrollment

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
study_id	INT	Study associated with the monthly enrollment
enrollment_month	DATE	First day of the enrollment month
enrolled_patients	INT	Number of patients enrolled during the month

studies

Column	Type	Description
study_idPK	INT	Unique study identifier
study_name	VARCHAR(150)	Study name

Your question is Rolling Six-Month Enrollment SQL. 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