Players Rated Above the Average MMR — SQL Subqueries Problem
Each ranked player has a matchmaking rating (mmr). Players still in their placement matches have no rating yet (mmr NULL) and must not affect the average.
- Difficulty: Easy
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #180
Problem statement
Each ranked player has a matchmaking rating (mmr). Players still in their placement matches have no rating yet (mmr NULL) and must not affect the average. The coaching programme invites players whose rating is strictly above the average rating of all rated players.
Return the columns player_id, gamertag and mmr, ordered by mmr descending, then player_id ascending.
Tables
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| gamertag | varchar |
| region | varchar |
| mmr | int |
Primary key: player_id.
mmr is NULL while the player is still in placement matches.
Examples
Example 1
Player
| player_id | gamertag | region | mmr |
|---|---|---|---|
| 1 | ViperVikram | IN | 2400 |
| 2 | ComboKavya | IN | 1800 |
| 3 | DriftDavid | EU | NULL |
| 4 | TurboTanvi | SEA | 2100 |
| 5 | BotSlayer | NA | 1500 |
| 6 | AceAisha | IN | 2400 |
Output
| player_id | gamertag | mmr |
|---|---|---|
| 1 | ViperVikram | 2400 |
| 6 | AceAisha | 2400 |
| 4 | TurboTanvi | 2100 |
How to solve Players Rated Above the Average MMR
A scalar subquery (SELECT AVG(mmr) FROM Player) returns one number, which the outer query compares with each player's rating. Aggregate functions ignore NULLs, so the average is taken over rated players only — exactly what the statement wants; SUM(mmr) / COUNT(mmr) is the same thing written out, while SUM(mmr) / COUNT(*) would be wrong because it counts the unrated players.
For a player in placement matches the comparison NULL > avg is unknown, so the row is filtered out without an extra condition. The comparison is strict: when every rated player has the same rating, nobody is above the average and the result is empty.
A window AVG(mmr) OVER () attaches the same average to every row in one pass, after which an outer query filters. The subquery version reads the table twice; both are linear.
Reference solution (MySQL)
SELECT player_id, gamertag, mmr
FROM Player
WHERE mmr > (SELECT AVG(mmr) FROM Player)
ORDER BY mmr DESC, player_idAnother way
SELECT p.player_id, p.gamertag, p.mmr FROM Player p CROSS JOIN (SELECT SUM(mmr) / COUNT(mmr) AS avg_mmr FROM Player) a WHERE p.mmr > a.avg_mmr ORDER BY p.mmr DESC, p.player_idAnother way
SELECT player_id, gamertag, mmr FROM (SELECT player_id, gamertag, mmr, AVG(mmr) OVER () AS avg_mmr FROM Player) t WHERE mmr > avg_mmr ORDER BY mmr DESC, player_id← Battle Pass Progress Labels for Season 12 · Player Win Rates With at Least Five Matches →