Rolling 7-Day Patient Visit Average | Dataford Interview Questions

How would you use SQL window functions to calculate a rolling 7-day average of patient visits by clinic for Remedy Partners’ analytics?

Use the supplied clinic and visit data. Include participating clinics and every calendar date from the earliest to latest visit date.

Output

One row per clinic and calendar date.
Return clinic_id, clinic_name, visit_date, daily_visits, and rolling_7_day_avg.
Include dates with zero visits, round the average to six decimal places, and order by clinic_id, then visit_date.

clinics

Column	Type	Description
clinic_idPK	INT	Unique clinic identifier
clinic_name	VARCHAR(100)	Clinic display name

patient_visits

Column	Type	Description
visit_idPK	INT	Unique visit identifier
clinic_id	INT	Clinic associated with the visit
patient_id	INT	Patient associated with the visit
visit_date	DATE	Date of the patient visit
visit_type	VARCHAR(50)	Type of clinical visit

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