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
| Column | Type |
|---|---|
| match_id | int |
| mode | enum(Battle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag) |
| map_name | varchar |
| status | enum(completed, abandoned) |
| duration_sec | int |
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_id | mode | map_name | status | duration_sec |
|---|---|---|---|---|
| 1 | Battle Royale | Erangel Docks | completed | 1620 |
| 2 | Battle Royale | Erangel Docks | completed | 1385 |
| 3 | Team Deathmatch | Old Delhi Bazaar | completed | 600 |
| 4 | Team Deathmatch | Warehouse 9 | abandoned | 95 |
| 5 | Ranked 5v5 | Ascent Fort | completed | 2310 |
| 6 | Team Deathmatch | Warehouse 9 | completed | 587 |
| 7 | Capture the Flag | Snow Peak | abandoned | 410 |
| 8 | Team Deathmatch | Old Delhi Bazaar | completed | 591 |
Output
| mode | completed_matches | avg_duration_sec |
|---|---|---|
| Ranked 5v5 | 1 | 2310 |
| Battle Royale | 2 | 1502.5 |
| Team Deathmatch | 3 | 592.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, modeAnother 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 →