Peak Ticket Hour for Each Day of the Week — SQL Problem
Workforce planning needs to know when tickets arrive. For each day of the week that has tickets, find the hour of the day (0–23, from created_at) in which…
- Difficulty: Medium
- Topics: Dates, Window Functions
- Dialect: MySQL
- Problem: #340
Problem statement
Workforce planning needs to know when tickets arrive. For each day of the week that has tickets, find the hour of the day (0–23, from created_at) in which the most tickets were created on that weekday, counting across all weeks. If two hours tie, take the earlier hour.
Return the columns weekday (the English day name, e.g. 'Monday'), peak_hour and tickets (tickets in that hour), ordered from Monday to Sunday.
Tables
Table: Ticket
| Column | Type |
|---|---|
| ticket_id | int |
| channel | enum(email, chat, phone, whatsapp) |
| created_at | datetime |
Primary key: ticket_id.
One row per ticket, with the moment it was created.
Examples
Example 1
Ticket
| ticket_id | channel | created_at |
|---|---|---|
| 1 | chat | 2025-06-02 10:15:00 |
| 2 | 2025-06-02 10:50:00 | |
| 3 | chat | 2025-06-09 10:05:00 |
| 4 | phone | 2025-06-09 14:30:00 |
| 5 | 2025-06-04 09:10:00 | |
| 6 | chat | 2025-06-04 18:20:00 |
| 7 | 2025-06-07 22:40:00 | |
| 8 | chat | 2025-06-14 22:05:00 |
| 9 | chat | 2025-06-14 11:00:00 |
Output
| weekday | peak_hour | tickets |
|---|---|---|
| Monday | 10 | 3 |
| Wednesday | 9 | 1 |
| Saturday | 22 | 2 |
How to solve Peak Ticket Hour for Each Day of the Week
Two steps: bucket, then pick the top bucket per group. The buckets are (weekday, hour) pairs: WEEKDAY(created_at), DAYNAME(created_at) and HOUR(created_at) in the GROUP BY, with COUNT(*) per bucket. Tickets from different weeks fall into the same bucket because only the weekday, not the date, is part of the key.
To keep one hour per weekday, number the buckets inside each weekday with ROW_NUMBER() OVER (PARTITION BY wd ORDER BY tickets DESC, hr) and keep number 1. The second sort key implements the tie rule — among equally busy hours the earlier one comes first — and makes the answer deterministic; RANK would return every tied hour.
The output order is by the numeric weekday, not by its name: DAYNAME values sort alphabetically (Friday before Monday). Carrying WEEKDAY through the query gives a key that runs Monday = 0 to Sunday = 6. The alternative keeps a bucket when no other bucket of the same weekday beats it — more tickets, or as many tickets in an earlier hour. With at most 7 × 24 buckets either form is instant once the tickets are grouped.
Reference solution (MySQL)
WITH hourly AS (
SELECT WEEKDAY(created_at) AS wd, DAYNAME(created_at) AS weekday, HOUR(created_at) AS hr, COUNT(*) AS tickets
FROM Ticket
GROUP BY WEEKDAY(created_at), DAYNAME(created_at), HOUR(created_at)
), ranked AS (
SELECT wd, weekday, hr, tickets,
ROW_NUMBER() OVER (PARTITION BY wd ORDER BY tickets DESC, hr) AS rn
FROM hourly
)
SELECT weekday, hr AS peak_hour, tickets
FROM ranked
WHERE rn = 1
ORDER BY wdAnother way
WITH hourly AS (
SELECT WEEKDAY(created_at) AS wd, DAYNAME(created_at) AS weekday, HOUR(created_at) AS hr, COUNT(*) AS tickets
FROM Ticket GROUP BY WEEKDAY(created_at), DAYNAME(created_at), HOUR(created_at)
)
SELECT h.weekday, h.hr AS peak_hour, h.tickets
FROM hourly h
WHERE NOT EXISTS (
SELECT 1 FROM hourly o
WHERE o.wd = h.wd AND (o.tickets > h.tickets OR (o.tickets = h.tickets AND o.hr < h.hr))
)
ORDER BY h.wd← Agent CSAT Rank Within Each Support Team · 90th Percentile API Latency per Endpoint →