Guild Members Above Their Guild's Average Points

Medium
189.

Guild Members Above Their Guild's Average Points

Each guild member earns season points from wins and events. Guild leaders want to shortlist their stronger players for the guild-war roster: members whose season_points are strictly greater than the average of their own guild. A guild with one member, or one where everyone has the same points, has nobody above its average.

Return guild_id, gamertag and season_points, ordered by guild_id, then season_points descending, then gamertag.

Tables

Table: GuildMember

ColumnType
player_idint
guild_idint
gamertagvarchar
season_pointsint

Primary key: player_id.

One row per player in a guild; a player is in at most one guild. Gamertags are unique.

Example

The rows must come back in the order shown.

GuildMember

player_idguild_idgamertagseason_points
110ShadowFang1200
210PixelPriya800
310LagLord700
410FrostDev1200
520KingKabir500
620ClutchDiya500
730AceAisha950
840BotSlayer300
940NovaNoah301

Output

guild_idgamertagseason_points
10FrostDev1200
10ShadowFang1200
40NovaNoah301
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.

Guild Members Above Their Guild's Average Points

Each guild member earns season points from wins and events. Guild leaders want to shortlist their stronger players for the guild-war roster: members whose…

  • Difficulty: Medium
  • Topics: Subqueries, Window Functions
  • Dialect: MySQL
  • Problem: #189

Problem statement

Each guild member earns season points from wins and events. Guild leaders want to shortlist their stronger players for the guild-war roster: members whose season_points are strictly greater than the average of their own guild. A guild with one member, or one where everyone has the same points, has nobody above its average.

Return guild_id, gamertag and season_points, ordered by guild_id, then season_points descending, then gamertag.

Tables

Table: GuildMember

ColumnType
player_idint
guild_idint
gamertagvarchar
season_pointsint

Primary key: player_id.

One row per player in a guild; a player is in at most one guild. Gamertags are unique.

Examples

Example 1

GuildMember

player_idguild_idgamertagseason_points
110ShadowFang1200
210PixelPriya800
310LagLord700
410FrostDev1200
520KingKabir500
620ClutchDiya500
730AceAisha950
840BotSlayer300
940NovaNoah301

Output

guild_idgamertagseason_points
10FrostDev1200
10ShadowFang1200
40NovaNoah301

How to solve Guild Members Above Their Guild's Average Points

Each member is compared with a number that depends on their own group — the classic correlated subquery: (SELECT AVG(season_points) FROM GuildMember o WHERE o.guild_id = g.guild_id) is re-evaluated for each outer row with that row's guild. Keep the rows where the points are strictly greater.

The edge cases follow from the arithmetic: a guild with one member has an average equal to that member's points, and a guild where everyone ties has an average equal to each of them, so the strict comparison returns nobody for either. Guild 40's 301 against an average of 300.5 is above, so NovaNoah is in.

Computing the averages once in a grouped derived table and joining, or attaching AVG(season_points) OVER (PARTITION BY guild_id) to every row, avoids re-running the subquery per row — most engines decorrelate the subquery into the join anyway. All three are linear after one grouping.

Reference solution (MySQL)

SELECT g.guild_id, g.gamertag, g.season_points
FROM GuildMember g
WHERE g.season_points > (SELECT AVG(o.season_points) FROM GuildMember o WHERE o.guild_id = g.guild_id)
ORDER BY g.guild_id, g.season_points DESC, g.gamertag

Another way

SELECT g.guild_id, g.gamertag, g.season_points FROM GuildMember g JOIN (SELECT guild_id, AVG(season_points) AS avg_points FROM GuildMember GROUP BY guild_id) a ON a.guild_id = g.guild_id WHERE g.season_points > a.avg_points ORDER BY g.guild_id, g.season_points DESC, g.gamertag

Another way

SELECT guild_id, gamertag, season_points FROM (SELECT guild_id, gamertag, season_points, AVG(season_points) OVER (PARTITION BY guild_id) AS avg_points FROM GuildMember) t WHERE season_points > avg_points ORDER BY guild_id, season_points DESC, gamertag

← Email Domains Behind Unverified Sign-ups · Each Player's First Store Purchase After Sign-up →