ICU Over-Capacity Streaks — SQL Window Functions Problem
The ICU files a census every night. A night is over capacity when occupied_beds is at least capacity (extra patients wait in step-down beds).
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #216
Problem statement
The ICU files a census every night. A night is over capacity when occupied_beds is at least capacity (extra patients wait in step-down beds). A missing census date is unknown, so it breaks a streak.
Return every run of three or more consecutive calendar dates that were all over capacity, with columns streak_start, streak_end and nights, ordered by streak_start.
Tables
Table: IcuCensus
| Column | Type |
|---|---|
| census_date | date |
| occupied_beds | int |
| capacity | int |
Primary key: census_date.
One row per night the census was filed; capacity can change when beds are added or closed.
Examples
Example 1
IcuCensus
| census_date | occupied_beds | capacity |
|---|---|---|
| 2025-01-01 | 18 | 20 |
| 2025-01-02 | 20 | 20 |
| 2025-01-03 | 22 | 20 |
| 2025-01-04 | 21 | 20 |
| 2025-01-05 | 19 | 20 |
| 2025-01-06 | 24 | 22 |
| 2025-01-07 | 23 | 22 |
| 2025-01-09 | 25 | 22 |
| 2025-01-10 | 22 | 22 |
| 2025-01-11 | 23 | 22 |
| 2025-01-12 | 22 | 22 |
Output
| streak_start | streak_end | nights |
|---|---|---|
| 2025-01-02 | 2025-01-04 | 3 |
| 2025-01-09 | 2025-01-12 | 4 |
How to solve ICU Over-Capacity Streaks
This is gaps and islands. After filtering to the over-capacity nights, every streak is a set of rows whose dates are consecutive. Number the remaining rows in date order with ROW_NUMBER(), and turn each date into a day number with DATEDIFF(census_date, '2000-01-01'). Inside one streak both rise by exactly one per row, so day number − row number is the same for the whole streak; a gap — a night under capacity or a missing census — makes the day number jump while the row number does not, which starts a new value.
Grouping by that difference gives one group per island: MIN and MAX of the dates are its ends and COUNT(*) its length, and HAVING COUNT(*) >= 3 keeps the real streaks. Note the >= in the over-capacity test: a night exactly at capacity counts.
The alternative marks each night that does not follow the previous over-capacity night by one day (LAG + DATEDIFF) as the start of a run, and a running SUM of those flags numbers the runs. Both sort the filtered rows once.
Reference solution (MySQL)
WITH overcap AS (
SELECT census_date,
DATEDIFF(census_date, '2000-01-01') - ROW_NUMBER() OVER (ORDER BY census_date) AS grp
FROM IcuCensus
WHERE occupied_beds >= capacity
)
SELECT MIN(census_date) AS streak_start, MAX(census_date) AS streak_end, COUNT(*) AS nights
FROM overcap
GROUP BY grp
HAVING COUNT(*) >= 3
ORDER BY streak_startAnother way
WITH flagged AS (
SELECT census_date,
CASE WHEN DATEDIFF(census_date, LAG(census_date) OVER (ORDER BY census_date)) = 1 THEN 0 ELSE 1 END AS starts_run
FROM IcuCensus WHERE occupied_beds >= capacity
), numbered AS (
SELECT census_date, SUM(starts_run) OVER (ORDER BY census_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_id FROM flagged
)
SELECT MIN(census_date) AS streak_start, MAX(census_date) AS streak_end, COUNT(*) AS nights
FROM numbered GROUP BY run_id HAVING COUNT(*) > 2 ORDER BY streak_start← Peak Arrival Hour in Emergency Each Day · Median Length of Stay by Ward →