Players Rated Above the Average MMR

Easy
180.

Players Rated Above the Average MMR

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

ColumnType
player_idint
gamertagvarchar
regionvarchar
mmrint

Primary key: player_id.

mmr is NULL while the player is still in placement matches.

Example

The rows must come back in the order shown.

Player

player_idgamertagregionmmr
1ViperVikramIN2400
2ComboKavyaIN1800
3DriftDavidEUNULL
4TurboTanviSEA2100
5BotSlayerNA1500
6AceAishaIN2400

Output

player_idgamertagmmr
1ViperVikram2400
6AceAisha2400
4TurboTanvi2100
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.

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

ColumnType
player_idint
gamertagvarchar
regionvarchar
mmrint

Primary key: player_id.

mmr is NULL while the player is still in placement matches.

Examples

Example 1

Player

player_idgamertagregionmmr
1ViperVikramIN2400
2ComboKavyaIN1800
3DriftDavidEUNULL
4TurboTanviSEA2100
5BotSlayerNA1500
6AceAishaIN2400

Output

player_idgamertagmmr
1ViperVikram2400
6AceAisha2400
4TurboTanvi2100

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_id

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

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