Patients Readmitted Within 30 Days of Discharge

Medium
207.

Patients Readmitted Within 30 Days of Discharge

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

ColumnType
admission_idint
patient_idint
admit_datedate
discharge_datedate

Primary key: admission_id.

One row per inpatient stay. discharge_date is NULL while the patient is still admitted.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Admission

admission_idpatient_idadmit_datedischarge_date
12012025-01-032025-01-08
22012025-01-202025-01-25
32012025-03-102025-03-12
42022025-02-012025-02-05
52022025-03-072025-03-09
62032025-02-112025-02-14
72032025-02-14NULL
82042025-01-152025-01-15

Output

admission_idpatient_iddays_since_discharge
220112
520230
72030
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
admission_idint
patient_idint
admit_datedate
discharge_datedate

Primary key: admission_id.

One row per inpatient stay. discharge_date is NULL while the patient is still admitted.

Examples

Example 1

Admission

admission_idpatient_idadmit_datedischarge_date
12012025-01-032025-01-08
22012025-01-202025-01-25
32012025-03-102025-03-12
42022025-02-012025-02-05
52022025-03-072025-03-09
62032025-02-112025-02-14
72032025-02-14NULL
82042025-01-152025-01-15

Output

admission_idpatient_iddays_since_discharge
220112
520230
72030

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 30

Another 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) <= 30

Another 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 →