Player Win Rates With at Least Five Matches

Medium
181.

Player Win Rates With at Least Five Matches

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

ColumnType
player_idint
gamertagvarchar

Primary key: player_id.

Gamertags are unique.

Table: MatchParticipant

ColumnTypeValues
match_idint
player_idint
teamenumred, blue
resultenumwin, loss, draw

Primary key (together): match_id, player_id.

One row per player per match they finished.

Example

The rows must come back in the order shown.

Player

player_idgamertag
1HeadshotHarsh
2MidlaneMeera
3CampingKaran
4ZeroPingZara

MatchParticipant

match_idplayer_idteamresult
5011redwin
5012blueloss
5014redwin
5021bluewin
5022redloss
5024bluewin
5031reddraw
5032bluedraw
5034reddraw
5041redloss
9 more rows

Output

gamertagmatcheswinswin_rate_pct
HeadshotHarsh5360
MidlaneMeera6350
ZeroPingZara6350
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.

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

ColumnType
player_idint
gamertagvarchar

Primary key: player_id.

Gamertags are unique.

Table: MatchParticipant

ColumnType
match_idint
player_idint
teamenum(red, blue)
resultenum(win, loss, draw)

Primary key: match_id, player_id.

One row per player per match they finished.

Examples

Example 1

Player

player_idgamertag
1HeadshotHarsh
2MidlaneMeera
3CampingKaran
4ZeroPingZara

MatchParticipant

match_idplayer_idteamresult
5011redwin
5012blueloss
5014redwin
5021bluewin
5022redloss
5024bluewin
5031reddraw
5032bluedraw
5034reddraw
5041redloss
5042bluewin
5044redloss
5051bluewin
5052redwin
5053redwin
5062redwin
5064blueloss
5073bluewin
5074redwin

Output

gamertagmatcheswinswin_rate_pct
HeadshotHarsh5360
MidlaneMeera6350
ZeroPingZara6350

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.gamertag

Another 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.gamertag

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