Median Completed Match Duration by Game Mode — SQL Problem
Averages are skewed by the odd marathon match, so the designers want the median duration of each mode's completed matches (abandoned ones are ignored).
- Difficulty: Hard
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #195
Problem statement
Averages are skewed by the odd marathon match, so the designers want the median duration of each mode's completed matches (abandoned ones are ignored). With an odd number of matches the median is the middle duration; with an even number it is the average of the two middle durations.
For each mode with at least one completed match, return mode, completed_matches and median_duration_sec rounded to 1 decimal place. Order by mode.
Tables
Table: GameMatch
| Column | Type |
|---|---|
| match_id | int |
| mode | enum(Battle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag) |
| status | enum(completed, abandoned) |
| duration_sec | int |
Primary key: match_id.
One row per hosted match.
Examples
Example 1
GameMatch
| match_id | mode | status | duration_sec |
|---|---|---|---|
| 1 | Battle Royale | completed | 1500 |
| 2 | Battle Royale | completed | 1320 |
| 3 | Battle Royale | completed | 3900 |
| 4 | Battle Royale | abandoned | 60 |
| 5 | Team Deathmatch | completed | 600 |
| 6 | Team Deathmatch | completed | 545 |
| 7 | Team Deathmatch | completed | 600 |
| 8 | Team Deathmatch | completed | 610 |
| 9 | Ranked 5v5 | completed | 2101 |
| 10 | Ranked 5v5 | completed | 2000 |
Output
| mode | completed_matches | median_duration_sec |
|---|---|---|
| Battle Royale | 3 | 1500 |
| Ranked 5v5 | 2 | 2050.5 |
| Team Deathmatch | 4 | 600 |
How to solve Median Completed Match Duration by Game Mode
SQL has no portable MEDIAN, so build it from positions. Over the completed matches of each mode, ROW_NUMBER() OVER (PARTITION BY mode ORDER BY duration_sec, match_id) gives each match its position in sorted order and COUNT(*) OVER (PARTITION BY mode) the size n. The middle positions are FLOOR((n+1)/2) and FLOOR((n+2)/2): for n = 3 both are 2, for n = 4 they are 2 and 3. Keeping those rows and averaging them gives the middle value for odd n and the mean of the two middle values for even n.
The match_id tie-break makes positions unique when durations repeat; the median does not depend on which of two equal durations sits where, but the row numbering must be total. Team Deathmatch (545, 600, 600, 610) has median 600; Ranked (2000, 2101) gives 2050.5, and Battle Royale's 3,900-second outlier does not move its median of 1,500 as it would move an average.
A set-based alternative: a value is a median candidate when at least half the rows are ≤ it and at least half are ≥ it; the median is the average of the distinct candidates. It is quadratic but needs no windows. The window version is one sort per mode.
Reference solution (MySQL)
WITH ranked AS (
SELECT mode, duration_sec,
ROW_NUMBER() OVER (PARTITION BY mode ORDER BY duration_sec, match_id) AS rn,
COUNT(*) OVER (PARTITION BY mode) AS cnt
FROM GameMatch
WHERE status = 'completed'
)
SELECT mode, MAX(cnt) AS completed_matches, ROUND(AVG(duration_sec), 1) AS median_duration_sec
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))
GROUP BY mode
ORDER BY modeAnother way
WITH done AS (SELECT match_id, mode, duration_sec FROM GameMatch WHERE status = 'completed')
SELECT d.mode,
(SELECT COUNT(*) FROM done c WHERE c.mode = d.mode) AS completed_matches,
ROUND(AVG(DISTINCT d.duration_sec), 1) AS median_duration_sec
FROM done d
WHERE (SELECT COUNT(*) FROM done x WHERE x.mode = d.mode AND x.duration_sec <= d.duration_sec) * 2 >= (SELECT COUNT(*) FROM done c WHERE c.mode = d.mode)
AND (SELECT COUNT(*) FROM done x WHERE x.mode = d.mode AND x.duration_sec >= d.duration_sec) * 2 >= (SELECT COUNT(*) FROM done c WHERE c.mode = d.mode)
GROUP BY d.mode
ORDER BY d.mode← Peak Concurrent Game Sessions per Server Region · New-Player Funnel From Install to First Purchase by Platform →