Rolling 7-Day Prescriptions Average | Dataford Interview Questions

What SQL query would you use to calculate a rolling 7-day average of daily prescriptions by region using window functions?

Use the prescriptions table. Treat each region and calendar date as one daily total. The rolling average should include the current date and prior six calendar days where daily data exists.

Output

One row per region and prescription date.
Columns: region, prescription_date, daily_prescriptions, and rolling_7_day_avg.
Order by region, then prescription_date ascending.

Output details

Round rolling_7_day_avg to two decimal places.
Preserve NULL for a daily total when all prescription counts are NULL, and ignore NULL daily totals when averaging; return NULL when none are non-NULL.

Schema

prescriptions

Column	Type	Description
prescription_idPK	INT	Unique prescription record identifier
region	VARCHAR(50)	Geographic region associated with the prescription
prescription_date	DATE	Date on which the prescription was recorded
prescription_count	INT	Number of prescriptions in the record

Your question is Rolling 7-Day Prescriptions Average. 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