Average Completed Match Length by Game Mode

Easy
177.

Average Completed Match Length by Game Mode

Game designers tune each mode's pacing from how long completed matches last. A match with status abandoned (every player left, or the server crashed) has no meaningful length and is ignored.

For each mode with at least one completed match, return mode, completed_matches and avg_duration_sec — the average duration_sec of its completed matches rounded to 2 decimal places. Order by avg_duration_sec descending, then mode ascending.

Tables

Table: GameMatch

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

Primary key: match_id.

One row per match the servers hosted. duration_sec is the time from the first spawn to the end screen (for abandoned matches, until the server closed it).

Example

The rows must come back in the order shown.

GameMatch

match_idmodemap_namestatusduration_sec
1Battle RoyaleErangel Dockscompleted1620
2Battle RoyaleErangel Dockscompleted1385
3Team DeathmatchOld Delhi Bazaarcompleted600
4Team DeathmatchWarehouse 9abandoned95
5Ranked 5v5Ascent Fortcompleted2310
6Team DeathmatchWarehouse 9completed587
7Capture the FlagSnow Peakabandoned410
8Team DeathmatchOld Delhi Bazaarcompleted591

Output

modecompleted_matchesavg_duration_sec
Ranked 5v512310
Battle Royale21502.5
Team Deathmatch3592.67
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.

Average Completed Match Length by Game Mode — SQL Problem

Game designers tune each mode's pacing from how long completed matches last.

  • Difficulty: Easy
  • Topics: Aggregation, Basics
  • Dialect: MySQL
  • Problem: #177

Problem statement

Game designers tune each mode's pacing from how long completed matches last. A match with status abandoned (every player left, or the server crashed) has no meaningful length and is ignored.

For each mode with at least one completed match, return mode, completed_matches and avg_duration_sec — the average duration_sec of its completed matches rounded to 2 decimal places. Order by avg_duration_sec descending, then mode ascending.

Tables

Table: GameMatch

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

Primary key: match_id.

One row per match the servers hosted. duration_sec is the time from the first spawn to the end screen (for abandoned matches, until the server closed it).

Examples

Example 1

GameMatch

match_idmodemap_namestatusduration_sec
1Battle RoyaleErangel Dockscompleted1620
2Battle RoyaleErangel Dockscompleted1385
3Team DeathmatchOld Delhi Bazaarcompleted600
4Team DeathmatchWarehouse 9abandoned95
5Ranked 5v5Ascent Fortcompleted2310
6Team DeathmatchWarehouse 9completed587
7Capture the FlagSnow Peakabandoned410
8Team DeathmatchOld Delhi Bazaarcompleted591

Output

modecompleted_matchesavg_duration_sec
Ranked 5v512310
Battle Royale21502.5
Team Deathmatch3592.67

How to solve Average Completed Match Length by Game Mode

Filter to completed matches, group by mode, and take COUNT(*) and AVG(duration_sec). The WHERE clause runs before the grouping, so abandoned matches neither count nor pull the average down, and a mode that only ever had abandoned matches has no rows left and disappears from the answer.

AVG of integers is a fraction; MySQL carries it to four decimal places, so the result is rounded to two with ROUND(…, 2) as the statement asks, and the sort uses that rounded value. Two modes with the same average are ordered by name.

The same numbers come out of conditional aggregation — sum the durations and count the matches with a CASE that only lets completed ones through, then divide — which is handy when you also want the abandoned count in the same row. Either way it is one pass over the table.

Reference solution (MySQL)

SELECT mode, COUNT(*) AS completed_matches, ROUND(AVG(duration_sec), 2) AS avg_duration_sec
FROM GameMatch
WHERE status = 'completed'
GROUP BY mode
ORDER BY avg_duration_sec DESC, mode

Another way

SELECT mode, completed_matches, ROUND(total / completed_matches, 2) AS avg_duration_sec FROM (
  SELECT mode, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_matches,
         SUM(CASE WHEN status = 'completed' THEN duration_sec ELSE 0 END) AS total
  FROM GameMatch GROUP BY mode
) t WHERE completed_matches > 0
ORDER BY avg_duration_sec DESC, mode

← Gamertags Breaking the Naming Policy · Daily Active Players From the Login Log →