Peak Arrival Hour in Emergency Each Day

Medium
215.

Peak Arrival Hour in Emergency Each Day

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

ColumnTypeValues
visit_idint
patient_idint
arrived_atdatetime
triageenumred, orange, yellow, green

Primary key: visit_id.

One row per emergency arrival; triage is the colour assigned at the door.

Example

The rows must come back in the order shown.

ErVisit

visit_idpatient_idarrived_attriage
14012025-03-08 09:05:00yellow
24022025-03-08 09:40:00green
34032025-03-08 14:10:00red
44042025-03-08 14:59:59orange
54052025-03-08 22:30:00green
64062025-03-09 01:15:00red
74072025-03-09 18:20:00yellow
84082025-03-09 18:45:00green
94092025-03-09 18:50:00orange

Output

visit_datepeak_hourarrivals
2025-03-0892
2025-03-09183
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.

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

ColumnType
visit_idint
patient_idint
arrived_atdatetime
triageenum(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_idpatient_idarrived_attriage
14012025-03-08 09:05:00yellow
24022025-03-08 09:40:00green
34032025-03-08 14:10:00red
44042025-03-08 14:59:59orange
54052025-03-08 22:30:00green
64062025-03-09 01:15:00red
74072025-03-09 18:20:00yellow
84082025-03-09 18:45:00green
94092025-03-09 18:50:00orange

Output

visit_datepeak_hourarrivals
2025-03-0892
2025-03-09183

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_date

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