Your question is Rolling Six-Month Enrollment SQL. 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.
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.
study_name, enrollment_month, monthly_enrollment, and six_month_rolling_avg.study_name, then enrollment_month ascending.| 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 |
| Column | Type | Description |
|---|---|---|
| study_idPK | INT | Unique study identifier |
| study_name | VARCHAR(150) | Study name |