Interview Question Bank · Running SUM · opening balance · generate_series
11. ICU beds occupied per day
HardHealthcareQuestion #143
Concept: Running SUM · opening balance · generate_seriesshow
Occupancy from in/out events is a running total: +1 for each arrival, -1 for each departure, summed in date order with SUM(...) OVER (ORDER BY day). Two things break it: days with no events vanish unless you generate a calendar, and anyone who arrived before your window is missing unless you add the opening balance (net arrivals before the first day).
Example (different data):
WITH cal AS ( SELECT g::date AS day FROM generate_series(TIMESTAMP '2025-01-01', TIMESTAMP '2025-01-07', INTERVAL '1 day') g ), d AS ( SELECT c.day, COUNT(m.id) FILTER (WHERE m.kind = 'join') - COUNT(m.id) FILTER (WHERE m.kind = 'leave') AS net FROM cal c LEFT JOIN memberships m ON m.at::date = c.day GROUP BY c.day ) SELECT day, (SELECT COUNT(*) FROM members_before_2025) + SUM(net) OVER (ORDER BY day) AS members FROM d;
Your task
Larkspur Health's ICU logs an 'in' event when a patient arrives and an 'out' event when they leave. The capacity planner wants a daily census for the first week of March 2025, including days when nobody moved.
For each day from 2025-03-01 to 2025-03-07, return day (date), admissions ('in' events that day), discharges ('out' events that day) and beds_occupied (patients in the ICU at the end of that day, counting everyone who arrived earlier and hasn't left). Sort by day.
Starting Postgres in your browser…
Checking your access…