Ranked Queue Wait Time by Hour of Day

Medium
183.

Ranked Queue Wait Time by Hour of Day

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

ColumnTypeValues
ticket_idint
player_idint
modeenumBattle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag
queued_atdatetime
matched_atdatetime

Primary key: ticket_id.

One row per time a player entered a queue. matched_at is NULL when no match was found.

Example

The rows must come back in the order shown.

QueueTicket

ticket_idplayer_idmodequeued_atmatched_at
121Ranked 5v52025-05-03 02:10:002025-05-03 02:13:20
222Ranked 5v52025-05-03 02:40:00NULL
323Ranked 5v52025-05-04 02:05:302025-05-04 02:09:00
424Battle Royale2025-05-03 02:11:002025-05-03 02:11:40
525Ranked 5v52025-05-03 20:00:002025-05-03 20:00:45
621Ranked 5v52025-05-03 20:59:592025-05-03 21:00:59
726Ranked 5v52025-05-04 04:20:00NULL

Output

queue_hourticketsunmatchedavg_wait_sec
231205
411NULL
202052.5
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.

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

ColumnType
ticket_idint
player_idint
modeenum(Battle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag)
queued_atdatetime
matched_atdatetime

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_idplayer_idmodequeued_atmatched_at
121Ranked 5v52025-05-03 02:10:002025-05-03 02:13:20
222Ranked 5v52025-05-03 02:40:00NULL
323Ranked 5v52025-05-04 02:05:302025-05-04 02:09:00
424Battle Royale2025-05-03 02:11:002025-05-03 02:11:40
525Ranked 5v52025-05-03 20:00:002025-05-03 20:00:45
621Ranked 5v52025-05-03 20:59:592025-05-03 21:00:59
726Ranked 5v52025-05-04 04:20:00NULL

Output

queue_hourticketsunmatchedavg_wait_sec
231205
411NULL
202052.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_hour

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