Daily Admissions With Quiet Days and a Running Total

Hard
222.

Daily Admissions With Quiet Days and a Running Total

The hospital's admissions chart must show every calendar day from the first admission date in the table to the last one, including quiet days with no admission (which have no row in Admission).

Return day, admissions (admissions that day, 0 on a quiet day) and running_total (all admissions from the first day up to and including this one), ordered by day. An empty table gives no rows.

Tables

Table: Admission

ColumnType
admission_idint
patient_idint
admit_datedate
wardvarchar

Primary key: admission_id.

One row per inpatient admission. The first and last admission dates are never more than a month apart.

Example

The rows must come back in the order shown.

Admission

admission_idpatient_idadmit_dateward
11012025-03-01General
21022025-03-01ICU
31032025-03-02General
41042025-03-05Maternity
51052025-03-05General
61062025-03-05Paediatric
71072025-03-07General

Output

dayadmissionsrunning_total
2025-03-0122
2025-03-0213
2025-03-0303
2025-03-0403
2025-03-0536
2025-03-0606
2025-03-0717
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.

Daily Admissions With Quiet Days and a Running Total

The hospital's admissions chart must show every calendar day from the first admission date in the table to the last one, including quiet days with no…

  • Difficulty: Hard
  • Topics: Dates, Window Functions, Subqueries
  • Dialect: MySQL
  • Problem: #222

Problem statement

The hospital's admissions chart must show every calendar day from the first admission date in the table to the last one, including quiet days with no admission (which have no row in Admission).

Return day, admissions (admissions that day, 0 on a quiet day) and running_total (all admissions from the first day up to and including this one), ordered by day. An empty table gives no rows.

Tables

Table: Admission

ColumnType
admission_idint
patient_idint
admit_datedate
wardvarchar

Primary key: admission_id.

One row per inpatient admission. The first and last admission dates are never more than a month apart.

Examples

Example 1

Admission

admission_idpatient_idadmit_dateward
11012025-03-01General
21022025-03-01ICU
31032025-03-02General
41042025-03-05Maternity
51052025-03-05General
61062025-03-05Paediatric
71072025-03-07General

Output

dayadmissionsrunning_total
2025-03-0122
2025-03-0213
2025-03-0303
2025-03-0403
2025-03-0536
2025-03-0606
2025-03-0717

How to solve Daily Admissions With Quiet Days and a Running Total

A GROUP BY over Admission can only produce days that have rows, so the quiet days have to be generated. A recursive CTE does that: the anchor is the first admission date, and each recursive step adds one day with DATE_ADD(day, INTERVAL 1 DAY) until the last admission date is reached. The table spans at most a month, so the recursion is short and always terminates.

LEFT JOIN the admissions onto the calendar on the date and group by day; COUNT(a.admission_id) counts 0 for a quiet day, where COUNT(*) would count the calendar row itself and say 1. The running total is a window sum over the days in order, with an explicit ROWS frame — the days are unique, so it is the same as the default here, but stating it avoids surprises.

On an empty table the anchor still returns one row, with a NULL day, and the recursion stops at once; WHERE day IS NOT NULL removes it so the answer is empty. The alternative uses correlated counts — admissions on the day and up to the day — instead of a join and a window.

Reference solution (MySQL)

WITH RECURSIVE days AS (
  SELECT MIN(admit_date) AS day FROM Admission
  UNION ALL
  SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM days
  WHERE day < (SELECT MAX(admit_date) FROM Admission)
), daily AS (
  SELECT d.day, COUNT(a.admission_id) AS admissions
  FROM days d
  LEFT JOIN Admission a ON a.admit_date = d.day
  WHERE d.day IS NOT NULL
  GROUP BY d.day
)
SELECT day, admissions,
       SUM(admissions) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily
ORDER BY day

Another way

WITH RECURSIVE days AS (
  SELECT MIN(admit_date) AS day, MAX(admit_date) AS last_day FROM Admission
  UNION ALL
  SELECT DATE_ADD(day, INTERVAL 1 DAY), last_day FROM days WHERE day < last_day
)
SELECT d.day,
       (SELECT COUNT(*) FROM Admission a WHERE a.admit_date = d.day) AS admissions,
       (SELECT COUNT(*) FROM Admission a WHERE a.admit_date <= d.day) AS running_total
FROM days d
WHERE d.day IS NOT NULL
ORDER BY d.day

← Double-Booked Doctor Appointment Slots · Employees Overdue for Probation Confirmation →