Patients Readmitted Within 30 Days of Discharge
A 30-day readmission is a quality indicator insurers and accreditation bodies watch.
- Difficulty: Medium
- Topics: Window Functions, Dates, Subqueries
- Dialect: MySQL
- Problem: #207
Problem statement
A 30-day readmission is a quality indicator insurers and accreditation bodies watch. An admission is a readmission when the same patient's previous admission was discharged 0 to 30 days before this admission's admit_date (a same-day return is 0 days). A patient's stays never overlap (no two start on the same day), and only the latest stay can still be open (discharge_date NULL).
Return admission_id, patient_id and days_since_discharge for every readmission, in any order. A patient's first admission is never a readmission.
Tables
Table: Admission
| Column | Type |
|---|---|
| admission_id | int |
| patient_id | int |
| admit_date | date |
| discharge_date | date |
Primary key: admission_id.
One row per inpatient stay. discharge_date is NULL while the patient is still admitted.
Examples
Example 1
Admission
| admission_id | patient_id | admit_date | discharge_date |
|---|---|---|---|
| 1 | 201 | 2025-01-03 | 2025-01-08 |
| 2 | 201 | 2025-01-20 | 2025-01-25 |
| 3 | 201 | 2025-03-10 | 2025-03-12 |
| 4 | 202 | 2025-02-01 | 2025-02-05 |
| 5 | 202 | 2025-03-07 | 2025-03-09 |
| 6 | 203 | 2025-02-11 | 2025-02-14 |
| 7 | 203 | 2025-02-14 | NULL |
| 8 | 204 | 2025-01-15 | 2025-01-15 |
Output
| admission_id | patient_id | days_since_discharge |
|---|---|---|
| 2 | 201 | 12 |
| 5 | 202 | 30 |
| 7 | 203 | 0 |
How to solve Patients Readmitted Within 30 Days of Discharge
The question compares each admission with the one before it for the same patient, which is what LAG is for. Partition the rows by patient_id, order each partition by admit_date, and LAG(discharge_date) hands every admission the discharge date of the stay just before it. DATEDIFF(admit_date, that date) is the gap in days; keep the rows where it is between 0 and 30.
Edge cases fall out naturally. A patient's first admission has no previous row, LAG returns NULL, the difference is NULL and the filter drops it. A same-day return gives 0 and counts; day 31 does not. The current open stay is only ever the latest, so its NULL discharge is never somebody's previous discharge.
Without window functions, find the previous admission with a self join plus NOT EXISTS (no admission between the two), or with a correlated MAX(discharge_date) over the earlier stays — valid here because stays never overlap, so the latest discharge belongs to the previous stay. The window version sorts each patient's rows once; the correlated forms probe the table per admission.
Reference solution (MySQL)
SELECT admission_id, patient_id, days_since_discharge
FROM (
SELECT admission_id, patient_id,
DATEDIFF(admit_date, LAG(discharge_date) OVER (PARTITION BY patient_id ORDER BY admit_date)) AS days_since_discharge
FROM Admission
) t
WHERE days_since_discharge BETWEEN 0 AND 30Another way
SELECT a.admission_id, a.patient_id, DATEDIFF(a.admit_date, p.discharge_date) AS days_since_discharge
FROM Admission a
JOIN Admission p ON p.patient_id = a.patient_id AND p.admit_date < a.admit_date
WHERE NOT EXISTS (SELECT 1 FROM Admission q WHERE q.patient_id = a.patient_id AND q.admit_date < a.admit_date AND q.admit_date > p.admit_date)
AND DATEDIFF(a.admit_date, p.discharge_date) <= 30Another way
SELECT admission_id, patient_id, gap AS days_since_discharge FROM (
SELECT a.admission_id, a.patient_id,
DATEDIFF(a.admit_date, (SELECT MAX(p.discharge_date) FROM Admission p WHERE p.patient_id = a.patient_id AND p.admit_date < a.admit_date)) AS gap
FROM Admission a
) t WHERE gap <= 30← OPD No-Show Rate by Doctor · Most Prescribed Drug in Each Department →