Rolling 30-Day Readmission Rate | Dataford Interview Questions

How would you use SQL window functions to calculate a rolling 30-day patient readmission rate?

Assume an admission is a readmission when the same patient was discharged previously and is admitted again within 30 days. Calculate the rate for each admission date using admissions during the preceding 30 calendar days, including the current date.

Output

One row per admission date with at least one admission
Columns: admission_date, readmissions_30d, admissions_30d, and readmission_rate
Sort by admission_date ascending

Schema

patients

Column	Type	Description
patient_idPK	VARCHAR(10)	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

admissions

Column	Type	Description
admission_idPK	INT	Unique admission identifier
patient_id	VARCHAR(10)	Patient associated with the admission
admission_date	DATE	Date the patient was admitted
discharge_date	DATE	Date the patient was discharged

Your question is Rolling 30-Day Readmission Rate. 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