Player Win Rates With at Least Five Matches — SQL Problem
Each row of MatchParticipant is one player's outcome in one match: win, loss or draw.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #181
Problem statement
Each row of MatchParticipant is one player's outcome in one match: win, loss or draw. The esports scouts look at win rates, but only for players with at least 5 matches — fewer is noise. A draw counts as a match played but not as a win.
Return gamertag, matches, wins and win_rate_pct = 100 × wins ÷ matches rounded to 2 decimal places, for every player with 5 or more matches. Order by win_rate_pct descending, then gamertag ascending.
Tables
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| gamertag | varchar |
Primary key: player_id.
Gamertags are unique.
Table: MatchParticipant
| Column | Type |
|---|---|
| match_id | int |
| player_id | int |
| team | enum(red, blue) |
| result | enum(win, loss, draw) |
Primary key: match_id, player_id.
One row per player per match they finished.
Examples
Example 1
Player
| player_id | gamertag |
|---|---|
| 1 | HeadshotHarsh |
| 2 | MidlaneMeera |
| 3 | CampingKaran |
| 4 | ZeroPingZara |
MatchParticipant
| match_id | player_id | team | result |
|---|---|---|---|
| 501 | 1 | red | win |
| 501 | 2 | blue | loss |
| 501 | 4 | red | win |
| 502 | 1 | blue | win |
| 502 | 2 | red | loss |
| 502 | 4 | blue | win |
| 503 | 1 | red | draw |
| 503 | 2 | blue | draw |
| 503 | 4 | red | draw |
| 504 | 1 | red | loss |
| 504 | 2 | blue | win |
| 504 | 4 | red | loss |
| 505 | 1 | blue | win |
| 505 | 2 | red | win |
| 505 | 3 | red | win |
| 506 | 2 | red | win |
| 506 | 4 | blue | loss |
| 507 | 3 | blue | win |
| 507 | 4 | red | win |
Output
| gamertag | matches | wins | win_rate_pct |
|---|---|---|---|
| HeadshotHarsh | 5 | 3 | 60 |
| MidlaneMeera | 6 | 3 | 50 |
| ZeroPingZara | 6 | 3 | 50 |
How to solve Player Win Rates With at Least Five Matches
Group MatchParticipant by player; within a group COUNT(*) is the matches played and SUM(CASE WHEN result = 'win' THEN 1 ELSE 0 END) the wins — conditional aggregation counts a subset of the rows without a second query. Draws and losses land in the count but add 0 to the wins.
The minimum of five matches is about the whole group, so it cannot go in WHERE (which sees one row at a time); HAVING COUNT(*) >= 5 filters groups after they are formed. Join to Player for the gamertag and group by both id and gamertag so ONLY_FULL_GROUP_BY is satisfied.
The rate is 100 * wins / matches, rounded to two places. Another neat form is AVG(IF(result = 'win', 100, 0)) — the mean of 100s and 0s is the percentage. Ties on the rate are broken by gamertag. One pass and one sort of the groups.
Reference solution (MySQL)
SELECT p.gamertag,
COUNT(*) AS matches,
SUM(CASE WHEN mp.result = 'win' THEN 1 ELSE 0 END) AS wins,
ROUND(100 * SUM(CASE WHEN mp.result = 'win' THEN 1 ELSE 0 END) / COUNT(*), 2) AS win_rate_pct
FROM MatchParticipant mp
JOIN Player p ON p.player_id = mp.player_id
GROUP BY p.player_id, p.gamertag
HAVING COUNT(*) >= 5
ORDER BY win_rate_pct DESC, p.gamertagAnother way
SELECT p.gamertag, s.matches, s.wins, ROUND(100 * s.wins / s.matches, 2) AS win_rate_pct
FROM (SELECT player_id, COUNT(*) AS matches, COUNT(CASE WHEN result = 'win' THEN 1 END) AS wins
FROM MatchParticipant GROUP BY player_id) s
JOIN Player p ON p.player_id = s.player_id
WHERE s.matches >= 5
ORDER BY win_rate_pct DESC, p.gamertagAnother way
SELECT p.gamertag, COUNT(*) AS matches, SUM(IF(mp.result = 'win', 1, 0)) AS wins,
ROUND(AVG(IF(mp.result = 'win', 100, 0)), 2) AS win_rate_pct
FROM Player p JOIN MatchParticipant mp ON mp.player_id = p.player_id
GROUP BY p.player_id, p.gamertag HAVING COUNT(*) >= 5
ORDER BY win_rate_pct DESC, p.gamertag← Players Rated Above the Average MMR · Top In-App Spender in Each Region for 2025 →