Peak Ticket Hour for Each Day of the Week

Medium
340.

Peak Ticket Hour for Each Day of the Week

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

ColumnTypeValues
ticket_idint
channelenumemail, chat, phone, whatsapp
created_atdatetime

Primary key: ticket_id.

One row per ticket, with the moment it was created.

Example

The rows must come back in the order shown.

Ticket

ticket_idchannelcreated_at
1chat2025-06-02 10:15:00
2email2025-06-02 10:50:00
3chat2025-06-09 10:05:00
4phone2025-06-09 14:30:00
5whatsapp2025-06-04 09:10:00
6chat2025-06-04 18:20:00
7email2025-06-07 22:40:00
8chat2025-06-14 22:05:00
9chat2025-06-14 11:00:00

Output

weekdaypeak_hourtickets
Monday103
Wednesday91
Saturday222
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 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

ColumnType
ticket_idint
channelenum(email, chat, phone, whatsapp)
created_atdatetime

Primary key: ticket_id.

One row per ticket, with the moment it was created.

Examples

Example 1

Ticket

ticket_idchannelcreated_at
1chat2025-06-02 10:15:00
2email2025-06-02 10:50:00
3chat2025-06-09 10:05:00
4phone2025-06-09 14:30:00
5whatsapp2025-06-04 09:10:00
6chat2025-06-04 18:20:00
7email2025-06-07 22:40:00
8chat2025-06-14 22:05:00
9chat2025-06-14 11:00:00

Output

weekdaypeak_hourtickets
Monday103
Wednesday91
Saturday222

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 wd

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