Median Length of Stay by Ward — SQL Window Functions Problem
Average length of stay is pulled up by a few very long admissions, so the bed-management team reports the median instead.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #217
Problem statement
Average length of stay is pulled up by a few very long admissions, so the bed-management team reports the median instead. A stay's length is DATEDIFF(discharge_date, admit_date) days (a same-day discharge is 0). Patients still admitted (discharge_date NULL) are excluded.
For every ward with at least one discharge, return ward, discharges and median_stay_days — the middle length, or the average of the two middle lengths when the count is even, rounded to 1 decimal. Order by ward.
Tables
Table: Admission
| Column | Type |
|---|---|
| admission_id | int |
| patient_id | int |
| ward | varchar |
| admit_date | date |
| discharge_date | date |
Primary key: admission_id.
discharge_date is NULL while the patient is still in the ward.
Examples
Example 1
Admission
| admission_id | patient_id | ward | admit_date | discharge_date |
|---|---|---|---|---|
| 1 | 101 | General | 2025-03-01 | 2025-03-04 |
| 2 | 102 | General | 2025-03-02 | 2025-03-03 |
| 3 | 103 | General | 2025-03-02 | 2025-03-30 |
| 4 | 104 | General | 2025-03-05 | NULL |
| 5 | 105 | ICU | 2025-03-01 | 2025-03-08 |
| 6 | 106 | ICU | 2025-03-03 | 2025-03-07 |
| 7 | 107 | ICU | 2025-03-04 | 2025-03-06 |
| 8 | 108 | ICU | 2025-03-04 | 2025-03-14 |
| 9 | 109 | Maternity | 2025-03-06 | 2025-03-06 |
| 10 | 110 | Paediatric | 2025-03-07 | NULL |
Output
| ward | discharges | median_stay_days |
|---|---|---|
| General | 3 | 3 |
| ICU | 4 | 5.5 |
| Maternity | 1 | 0 |
How to solve Median Length of Stay by Ward
SQL has no MEDIAN aggregate in MySQL, so the median is built from window functions. Compute each discharged stay's length with DATEDIFF, then in each ward number the stays in order of length (ROW_NUMBER, with admission_id as a tie-break so the numbering is fixed) and attach the ward's count n to every row with COUNT(*) OVER (PARTITION BY ward).
The median sits at positions ⌊(n+1)/2⌋ and ⌊(n+2)/2⌋: for n = 3 both are 2, for n = 4 they are 2 and 3. Keep the rows at those positions and average them per ward — one value for an odd count, the mean of two for an even one. Equal lengths do not matter: whichever of the tied rows takes a middle position carries the same value. Rounded to 1 decimal, since the mean of two whole numbers can end in .5.
The window-free version uses the definition of the median: a value with at least half the stays at or below it and at least half at or above it. Those qualifying values are the one or two middle values, and AVG(DISTINCT …) of them is the median. It is quadratic per ward; the window version sorts each ward once.
Reference solution (MySQL)
WITH stays AS (
SELECT ward, DATEDIFF(discharge_date, admit_date) AS los,
ROW_NUMBER() OVER (PARTITION BY ward ORDER BY DATEDIFF(discharge_date, admit_date), admission_id) AS rn,
COUNT(*) OVER (PARTITION BY ward) AS n
FROM Admission
WHERE discharge_date IS NOT NULL
)
SELECT ward, MAX(n) AS discharges, ROUND(AVG(los), 1) AS median_stay_days
FROM stays
WHERE rn IN (FLOOR((n + 1) / 2), FLOOR((n + 2) / 2))
GROUP BY ward
ORDER BY wardAnother way
WITH stays AS (
SELECT admission_id, ward, DATEDIFF(discharge_date, admit_date) AS los FROM Admission WHERE discharge_date IS NOT NULL
)
SELECT s.ward,
(SELECT COUNT(*) FROM stays c WHERE c.ward = s.ward) AS discharges,
ROUND(AVG(DISTINCT s.los), 1) AS median_stay_days
FROM stays s
WHERE (SELECT COUNT(*) FROM stays a WHERE a.ward = s.ward AND a.los <= s.los) * 2 >= (SELECT COUNT(*) FROM stays c WHERE c.ward = s.ward)
AND (SELECT COUNT(*) FROM stays b WHERE b.ward = s.ward AND b.los >= s.los) * 2 >= (SELECT COUNT(*) FROM stays c WHERE c.ward = s.ward)
GROUP BY s.ward
ORDER BY s.ward← ICU Over-Capacity Streaks · Patients With Three or More No-Shows in a Row →