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
| Column | Type |
|---|---|
| player_id | int |
| guild_id | int |
| gamertag | varchar |
| season_points | int |
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_id | guild_id | gamertag | season_points |
|---|---|---|---|
| 1 | 10 | ShadowFang | 1200 |
| 2 | 10 | PixelPriya | 800 |
| 3 | 10 | LagLord | 700 |
| 4 | 10 | FrostDev | 1200 |
| 5 | 20 | KingKabir | 500 |
| 6 | 20 | ClutchDiya | 500 |
| 7 | 30 | AceAisha | 950 |
| 8 | 40 | BotSlayer | 300 |
| 9 | 40 | NovaNoah | 301 |
Output
| guild_id | gamertag | season_points |
|---|---|---|
| 10 | FrostDev | 1200 |
| 10 | ShadowFang | 1200 |
| 40 | NovaNoah | 301 |
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.gamertagAnother 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.gamertagAnother 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 →