The 30-day readmission rate per department
Health regulators penalise hospitals whose patients bounce back too soon. A 30-day readmission happens when a patient is admitted again (to any department) at most 30 days after the discharge_date of an earlier stay. The earlier stay is the index stay and is charged to its own department. Only the very next admission of the patient counts, and only discharged index stays can be measured.
Task: return department (name), discharges (index stays with a discharge date), readmissions_30d (index stays followed by the patient's next admission within 30 days, a gap of exactly 30 days counts) and readmission_rate_pct (ROUND(100.0 * readmissions_30d / discharges, 2)). Sort by readmission_rate_pct from high to low, then department A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| admission_id INTEGER | patient_id INTEGER | doctor_id INTEGER | dept_id INTEGER | admit_date TEXT | discharge_date TEXT | diagnosis TEXT | total_cost_usd INTEGER |
|---|---|---|---|---|---|---|---|
| 1 | 39 | 14 | 2 | 2024-08-03 | 2024-08-11 | Tumor resection | 15320 |
| 2 | 11 | 6 | 6 | 2024-08-12 | 2024-08-15 | Poisoning | 4480 |
| 3 | 52 | 5 | 5 | 2024-09-17 | 2024-09-28 | Dehydration | 12560 |
| 4 | 25 | 11 | 5 | 2024-09-18 | 2024-09-27 | Asthma attack | 9390 |
| 5 | 5 | 11 | 5 | 2024-09-20 | 2024-09-29 | Dehydration | 12860 |
| 6 | 22 | 13 | 1 | 2024-10-16 | 2024-10-28 | Chest pain | 18330 |
Showing the first 6 of 99 rows.
| dept_id INTEGER | name TEXT | hospital TEXT |
|---|---|---|
| 1 | Cardiology | Meridian Central Hospital |
| 2 | Oncology | Meridian Central Hospital |
| 3 | Orthopedics | Lakeshore Medical Center |
| 4 | Neurology | Meridian Central Hospital |
| 5 | Pediatrics | Lakeshore Medical Center |
| 6 | Emergency Medicine | Lakeshore Medical Center |
| doctor_id INTEGER | name TEXT | dept_id INTEGER | country TEXT |
|---|---|---|---|
| 1 | Dr. Jakub Zielinski | 1 | Poland |
| 2 | Dr. Lucia Romero | 2 | Spain |
| 3 | Dr. Tunde Adeyemi | 3 | Nigeria |
| 4 | Dr. Rohan Nair | 4 | India |
| 5 | Dr. Elena Vidal | 5 | Spain |
| 6 | Dr. Thiago Pereira | 6 | Brazil |
Showing the first 6 of 15 rows.
| lab_id INTEGER | admission_id INTEGER | patient_id INTEGER | test_name TEXT | unit TEXT | lab_date TEXT | result_value REAL | ref_low REAL | ref_high REAL |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 39 | HbA1c | % | 2024-08-04 | 8.43 | 4 | 5.7 |
| 2 | 2 | 11 | Creatinine | mg/dL | 2024-08-12 | 1.04 | 0.6 | 1.3 |
| 3 | 3 | 52 | LDL Cholesterol | mg/dL | 2024-09-18 | 149.29 | 50 | 130 |
| 4 | 4 | 25 | LDL Cholesterol | mg/dL | 2024-09-18 | 92.08 | 50 | 130 |
| 5 | 5 | 5 | Creatinine | mg/dL | 2024-09-21 | 1.66 | 0.6 | 1.3 |
| 6 | 6 | 22 | LDL Cholesterol | mg/dL | 2024-10-16 | 172.15 | 50 | 130 |
Showing the first 6 of 110 rows.
| patient_id INTEGER | name TEXT | gender TEXT | birth_date TEXT | country TEXT |
|---|---|---|---|---|
| 1 | Ethan Reed | F | 1995-04-12 | United States |
| 2 | Hanna Nowak | F | 1972-04-23 | Poland |
| 3 | Karim Salem | M | 1976-06-17 | Egypt |
| 4 | Jules Girard | F | 1993-09-05 | France |
| 5 | Hannah Hartmann | F | 2017-04-08 | Germany |
| 6 | Lucia Navarro | F | 1974-06-27 | Spain |
Showing the first 6 of 60 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
LEAD() over each patient's admissions, ordered by date, brings the next admit date next to the current discharge date. Compute the window before you filter out open stays.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.