Rolling Average Satisfaction SQL | Dataford Interview Questions

Write a SQL query using window functions to calculate the rolling average of patient satisfaction scores over time.

Use the patient satisfaction score records provided. For each record, calculate the average of its score and the two immediately preceding records in chronological order. Ignore NULL scores when calculating averages.

Output

Return one row per score record with score_id, patient_id, score_date, satisfaction_score, and rolling_average.
Order by score_date ascending, then score_id ascending to break date ties.

Schema

patient_satisfaction_scores

Column	Type	Description
score_idPK	INT	Unique identifier for the satisfaction score record
patient_id	VARCHAR(20)	Identifier of the patient who submitted the score
score_date	DATE	Date on which the satisfaction score was recorded
satisfaction_score	NUMERIC(2,1)	Patient satisfaction score on a scale from 0 to 5

Your question is Rolling Average Satisfaction SQL. 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

Write a SQL query using window functions to calculate the rolling average of patient satisfaction scores over time.

Output

Return one row per score record with score_id, patient_id, score_date, satisfaction_score, and rolling_average.
Order by score_date ascending, then score_id ascending to break date ties.

Schema

patient_satisfaction_scores

Column	Type	Description
score_idPK	INT	Unique identifier for the satisfaction score record
patient_id	VARCHAR(20)	Identifier of the patient who submitted the score
score_date	DATE	Date on which the satisfaction score was recorded
satisfaction_score	NUMERIC(2,1)	Patient satisfaction score on a scale from 0 to 5

Your question is Rolling Average Satisfaction SQL. 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.