Peak Arrival Hour in Emergency Each Day — SQL Dates Problem
To roster triage nurses, the emergency department wants the busiest hour of each day. An arrival at 14:59:59 belongs to hour 14.
- Difficulty: Medium
- Topics: Dates, Window Functions
- Dialect: MySQL
- Problem: #215
Problem statement
To roster triage nurses, the emergency department wants the busiest hour of each day. An arrival at 14:59:59 belongs to hour 14.
For every date with at least one arrival, return visit_date, peak_hour (0–23, the hour with the most arrivals that day) and arrivals (how many arrived in that hour). If hours tie, the earliest hour wins. Order by visit_date.
Tables
Table: ErVisit
| Column | Type |
|---|---|
| visit_id | int |
| patient_id | int |
| arrived_at | datetime |
| triage | enum(red, orange, yellow, green) |
Primary key: visit_id.
One row per emergency arrival; triage is the colour assigned at the door.
Examples
Example 1
ErVisit
| visit_id | patient_id | arrived_at | triage |
|---|---|---|---|
| 1 | 401 | 2025-03-08 09:05:00 | yellow |
| 2 | 402 | 2025-03-08 09:40:00 | green |
| 3 | 403 | 2025-03-08 14:10:00 | red |
| 4 | 404 | 2025-03-08 14:59:59 | orange |
| 5 | 405 | 2025-03-08 22:30:00 | green |
| 6 | 406 | 2025-03-09 01:15:00 | red |
| 7 | 407 | 2025-03-09 18:20:00 | yellow |
| 8 | 408 | 2025-03-09 18:45:00 | green |
| 9 | 409 | 2025-03-09 18:50:00 | orange |
Output
| visit_date | peak_hour | arrivals |
|---|---|---|
| 2025-03-08 | 9 | 2 |
| 2025-03-09 | 18 | 3 |
How to solve Peak Arrival Hour in Emergency Each Day
Bucket first, then choose. DATE(arrived_at) and HOUR(arrived_at) turn each timestamp into a (day, hour) bucket — HOUR truncates, so 14:59:59 is hour 14 — and a GROUP BY on both with COUNT(*) gives the arrivals per bucket.
Choosing one bucket per day is a top-1 per group problem with a tie rule. ROW_NUMBER() OVER (PARTITION BY visit_date ORDER BY arrivals DESC, hr) numbers each day's hours from busiest to quietest, putting the earlier hour first among equal counts, so row 1 is exactly the statement's peak hour. RANK would return every tied hour, which is not what is asked.
Without windows, keep a bucket when no other bucket of the same day beats it — more arrivals, or equal arrivals at an earlier hour — with NOT EXISTS. Days without arrivals have no rows and so no output, as the statement says. The aggregation is one pass; the ranking sorts at most 24 rows per day.
Reference solution (MySQL)
WITH hourly AS (
SELECT DATE(arrived_at) AS visit_date, HOUR(arrived_at) AS hr, COUNT(*) AS arrivals
FROM ErVisit
GROUP BY DATE(arrived_at), HOUR(arrived_at)
)
SELECT visit_date, hr AS peak_hour, arrivals
FROM (
SELECT visit_date, hr, arrivals,
ROW_NUMBER() OVER (PARTITION BY visit_date ORDER BY arrivals DESC, hr) AS rn
FROM hourly
) ranked
WHERE rn = 1
ORDER BY visit_dateAnother way
WITH hourly AS (
SELECT LEFT(arrived_at, 10) AS visit_date, HOUR(arrived_at) AS hr, COUNT(*) AS arrivals
FROM ErVisit GROUP BY LEFT(arrived_at, 10), HOUR(arrived_at)
)
SELECT h.visit_date, h.hr AS peak_hour, h.arrivals FROM hourly h
WHERE NOT EXISTS (SELECT 1 FROM hourly o WHERE o.visit_date = h.visit_date AND (o.arrivals > h.arrivals OR (o.arrivals = h.arrivals AND o.hr < h.hr)))
ORDER BY h.visit_date← Medicines Below Their Reorder Level · ICU Over-Capacity Streaks →