Use CTEs and self-joins for CMS-style readmission methodology
Build me a readmission analysis using CTEs. Step 1: identify all inpatient discharges (exclude current admits). Step 2: flag any where the same patient had another inpatient admission within 30 days of discharge. Step 3: group by insurance type (normalized with LOWER) and show total discharges, readmissions, and rate as a percentage. This is the methodology CMS uses for HRRP.
| Column | Type | Key |
|---|---|---|
| encounter_id | INT | PK |
| patient_id | INT | FK → dim_patients |
| provider_id | INT | FK → dim_providers |
| department_id | INT | FK → dim_departments |
| admit_date | TEXT | |
| discharge_date | TEXT | |
| diagnosis_code | TEXT | |
| diagnosis_description | TEXT | |
| encounter_type | TEXT | |
| discharge_disposition | TEXT |
| Column | Type | Key |
|---|---|---|
| patient_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| date_of_birth | TEXT | |
| gender | TEXT | |
| insurance_type | TEXT | |
| zip_code | TEXT | |
| primary_language | TEXT | |
| is_deceased | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Build this in stages: CTE 1 holds inpatient discharges. A self-join on that CTE finds cases where the same patient returned within 30 days
The second admission must be AFTER the first discharge AND within 30 days. And make sure you're not matching a discharge to itself
`ON a.patient_id = b.patient_id AND b.admit_date > ___ AND b.admit_date <= ___ + INTERVAL '30 days' AND a.encounter_id ___ b.encounter_id` — then `GROUP BY LOWER(___)`