ICU Over-Capacity Streaks

Hard
216.

ICU Over-Capacity Streaks

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

ColumnType
census_datedate
occupied_bedsint
capacityint

Primary key: census_date.

One row per night the census was filed; capacity can change when beds are added or closed.

Example

The rows must come back in the order shown.

IcuCensus

census_dateoccupied_bedscapacity
2025-01-011820
2025-01-022020
2025-01-032220
2025-01-042120
2025-01-051920
2025-01-062422
2025-01-072322
2025-01-092522
2025-01-102222
2025-01-112322
1 more row

Output

streak_startstreak_endnights
2025-01-022025-01-043
2025-01-092025-01-124
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.

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

ColumnType
census_datedate
occupied_bedsint
capacityint

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_dateoccupied_bedscapacity
2025-01-011820
2025-01-022020
2025-01-032220
2025-01-042120
2025-01-051920
2025-01-062422
2025-01-072322
2025-01-092522
2025-01-102222
2025-01-112322
2025-01-122222

Output

streak_startstreak_endnights
2025-01-022025-01-043
2025-01-092025-01-124

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_start

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