Rolling 30-Day Patient Visits SQL | Dataford Interview Questions

Write a complex SQL query utilizing window functions to track patient visits over a rolling 30-day window.

Use the provided patient, provider, and visit data. Include completed visits only, and count completed visits for the same patient from the current visit date through the preceding 29 calendar days.

Output

One row per completed visit, including visit_id, patient_id, patient_name, visit_date, provider_name, and rolling_30_day_visits
Use Unknown when a visit has no matching provider
Order by patient_id, visit_date, then visit_id

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient's display name
date_of_birth	DATE	Patient date of birth

providers

Column	Type	Description
provider_idPK	INT	Unique provider identifier
provider_name	VARCHAR(100)	Provider's display name
specialty	VARCHAR(80)	Provider clinical specialty

visits

Column	Type	Description
visit_idPK	INT	Unique visit identifier
patient_id	INT	Patient receiving care
provider_id	INT	Provider associated with the visit
visit_date	DATE	Date of the visit
status	VARCHAR(20)	Visit status

Your question is Rolling 30-Day Patient Visits SQL. Start with the requirements and the three 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