Median Length of Stay by Ward

Hard
217.

Median Length of Stay by Ward

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

ColumnType
admission_idint
patient_idint
wardvarchar
admit_datedate
discharge_datedate

Primary key: admission_id.

discharge_date is NULL while the patient is still in the ward.

Example

The rows must come back in the order shown.

Admission

admission_idpatient_idwardadmit_datedischarge_date
1101General2025-03-012025-03-04
2102General2025-03-022025-03-03
3103General2025-03-022025-03-30
4104General2025-03-05NULL
5105ICU2025-03-012025-03-08
6106ICU2025-03-032025-03-07
7107ICU2025-03-042025-03-06
8108ICU2025-03-042025-03-14
9109Maternity2025-03-062025-03-06
10110Paediatric2025-03-07NULL

Output

warddischargesmedian_stay_days
General33
ICU45.5
Maternity10
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.

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

ColumnType
admission_idint
patient_idint
wardvarchar
admit_datedate
discharge_datedate

Primary key: admission_id.

discharge_date is NULL while the patient is still in the ward.

Examples

Example 1

Admission

admission_idpatient_idwardadmit_datedischarge_date
1101General2025-03-012025-03-04
2102General2025-03-022025-03-03
3103General2025-03-022025-03-30
4104General2025-03-05NULL
5105ICU2025-03-012025-03-08
6106ICU2025-03-032025-03-07
7107ICU2025-03-042025-03-06
8108ICU2025-03-042025-03-14
9109Maternity2025-03-062025-03-06
10110Paediatric2025-03-07NULL

Output

warddischargesmedian_stay_days
General33
ICU45.5
Maternity10

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 ward

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