Drill 11 of 12 in Healthcare questions
0

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…