Rolling 7-Day Patient Visit Average

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

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

You need to log in / sign up to run or submit.

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

Tablesclinicspatient_visits

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results