Median Completed Match Duration by Game Mode

Hard
195.

Median Completed Match Duration by Game Mode

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

ColumnTypeValues
match_idint
modeenumBattle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag
statusenumcompleted, abandoned
duration_secint

Primary key: match_id.

One row per hosted match.

Example

The rows must come back in the order shown.

GameMatch

match_idmodestatusduration_sec
1Battle Royalecompleted1500
2Battle Royalecompleted1320
3Battle Royalecompleted3900
4Battle Royaleabandoned60
5Team Deathmatchcompleted600
6Team Deathmatchcompleted545
7Team Deathmatchcompleted600
8Team Deathmatchcompleted610
9Ranked 5v5completed2101
10Ranked 5v5completed2000

Output

modecompleted_matchesmedian_duration_sec
Battle Royale31500
Ranked 5v522050.5
Team Deathmatch4600
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.

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

ColumnType
match_idint
modeenum(Battle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag)
statusenum(completed, abandoned)
duration_secint

Primary key: match_id.

One row per hosted match.

Examples

Example 1

GameMatch

match_idmodestatusduration_sec
1Battle Royalecompleted1500
2Battle Royalecompleted1320
3Battle Royalecompleted3900
4Battle Royaleabandoned60
5Team Deathmatchcompleted600
6Team Deathmatchcompleted545
7Team Deathmatchcompleted600
8Team Deathmatchcompleted610
9Ranked 5v5completed2101
10Ranked 5v5completed2000

Output

modecompleted_matchesmedian_duration_sec
Battle Royale31500
Ranked 5v522050.5
Team Deathmatch4600

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 mode

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