Rolling Average by Department | Dataford Interview Questions

Write a SQL query to calculate the rolling average of patient satisfaction scores over a 30-day period for each hospital department. Use the provided department and satisfaction score records. Treat the current date and the preceding 29 calendar days as the 30-day period, and ignore NULL scores when calculating averages.

Output

One row per department and score date with at least one recorded score
Columns: department_name, score_date, and rolling_30_day_average
Round the rolling average to two decimal places and sort by department name, then score date

Schema

departments

Column	Type	Description
department_idPK	INT	Unique hospital department identifier
department_name	VARCHAR(100)	Hospital department name

satisfaction_scores

Column	Type	Description
score_idPK	INT	Unique satisfaction score identifier
department_id	INT	Department associated with the score
score_date	DATE	Date the satisfaction score was recorded
satisfaction_score	DECIMAL(5,2)	Patient satisfaction score

Your question is Rolling Average by Department. 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