Ranked Queue Wait Time by Hour of Day — SQL Dates Problem
The matchmaking team suspects that ranked queues are slow at night.
- Difficulty: Medium
- Topics: Dates, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #183
Problem statement
The matchmaking team suspects that ranked queues are slow at night. Each QueueTicket records when a player entered the queue and, if a match was found, when; matched_at is NULL for a ticket that timed out or was cancelled. Only tickets for mode Ranked 5v5 matter.
For each hour of the day (queue_hour, 0–23, from queued_at) that has at least one ranked ticket, return queue_hour, tickets, unmatched (tickets with no match) and avg_wait_sec — the average number of seconds from queued_at to matched_at over the matched tickets, rounded to 2 decimal places, or NULL when no ticket of that hour was matched. Order by queue_hour.
Tables
Table: QueueTicket
| Column | Type |
|---|---|
| ticket_id | int |
| player_id | int |
| mode | enum(Battle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag) |
| queued_at | datetime |
| matched_at | datetime |
Primary key: ticket_id.
One row per time a player entered a queue. matched_at is NULL when no match was found.
Examples
Example 1
QueueTicket
| ticket_id | player_id | mode | queued_at | matched_at |
|---|---|---|---|---|
| 1 | 21 | Ranked 5v5 | 2025-05-03 02:10:00 | 2025-05-03 02:13:20 |
| 2 | 22 | Ranked 5v5 | 2025-05-03 02:40:00 | NULL |
| 3 | 23 | Ranked 5v5 | 2025-05-04 02:05:30 | 2025-05-04 02:09:00 |
| 4 | 24 | Battle Royale | 2025-05-03 02:11:00 | 2025-05-03 02:11:40 |
| 5 | 25 | Ranked 5v5 | 2025-05-03 20:00:00 | 2025-05-03 20:00:45 |
| 6 | 21 | Ranked 5v5 | 2025-05-03 20:59:59 | 2025-05-03 21:00:59 |
| 7 | 26 | Ranked 5v5 | 2025-05-04 04:20:00 | NULL |
Output
| queue_hour | tickets | unmatched | avg_wait_sec |
|---|---|---|---|
| 2 | 3 | 1 | 205 |
| 4 | 1 | 1 | NULL |
| 20 | 2 | 0 | 52.5 |
How to solve Ranked Queue Wait Time by Hour of Day
Bucket the ranked tickets by HOUR(queued_at) — the hour of the day, so 02:10 on two different dates falls into the same bucket, which is the point of the question. A ticket queued at 20:59:59 and matched at 21:00:59 belongs to hour 20, because the bucket comes from when it was queued.
For each ticket, TIMESTAMPDIFF(SECOND, queued_at, matched_at) is the wait; for an unmatched ticket it is NULL. That NULL is exactly what we want: AVG ignores NULLs, so the average is over matched tickets only, and an hour where nothing matched averages to NULL. COUNT(*) still counts every ticket, and SUM(CASE WHEN matched_at IS NULL THEN 1 ELSE 0 END) — or COUNT(*) - COUNT(matched_at) — counts the unmatched ones.
The alternative reads the hour from the text with SUBSTRING and divides a sum by NULLIF(COUNT(wait), 0), which also yields NULL for an hour with no matches. One scan and a grouping over at most 24 buckets.
Reference solution (MySQL)
SELECT HOUR(queued_at) AS queue_hour,
COUNT(*) AS tickets,
SUM(CASE WHEN matched_at IS NULL THEN 1 ELSE 0 END) AS unmatched,
ROUND(AVG(TIMESTAMPDIFF(SECOND, queued_at, matched_at)), 2) AS avg_wait_sec
FROM QueueTicket
WHERE mode = 'Ranked 5v5'
GROUP BY HOUR(queued_at)
ORDER BY queue_hourAnother way
SELECT queue_hour, COUNT(*) AS tickets, COUNT(*) - COUNT(matched_at) AS unmatched,
ROUND(SUM(wait) / NULLIF(COUNT(wait), 0), 2) AS avg_wait_sec
FROM (
SELECT CAST(SUBSTRING(queued_at, 12, 2) AS SIGNED) AS queue_hour, matched_at,
CASE WHEN matched_at IS NOT NULL THEN TIMESTAMPDIFF(SECOND, queued_at, matched_at) END AS wait
FROM QueueTicket WHERE mode = 'Ranked 5v5'
) t
GROUP BY queue_hour
ORDER BY queue_hour← Top In-App Spender in Each Region for 2025 · Players Reported for Cheating by Three Reporters in a Month →