Guilds With Three Active Members in the Last Week
Guild wars next season are open only to guilds that are actually playing.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #187
Problem statement
Guild wars next season are open only to guilds that are actually playing. The cut-off report runs on 30 June 2025: a member is active if their last_match_on falls in the 7 days ending that day — from 2025-06-24 to 2025-06-30 inclusive. A NULL last_match_on means the player has never finished a match. A guild qualifies with at least 3 active members.
Return guild_name, members (all members of the guild) and active_members, ordered by active_members descending, then guild_name.
Tables
Table: Guild
| Column | Type |
|---|---|
| guild_id | int |
| guild_name | varchar |
Primary key: guild_id.
Guild names are unique.
Table: GuildMember
| Column | Type |
|---|---|
| guild_id | int |
| player_id | int |
| role | enum(leader, officer, member) |
Primary key: player_id.
A player belongs to at most one guild.
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| gamertag | varchar |
| last_match_on | date |
Primary key: player_id.
last_match_on is the day of the player's latest finished match (never after 30 June 2025), or NULL.
Examples
Example 1
Guild
| guild_id | guild_name |
|---|---|
| 1 | Rajput Raiders |
| 2 | Midnight Snipers |
| 3 | Chai Squad |
GuildMember
| guild_id | player_id | role |
|---|---|---|
| 1 | 101 | leader |
| 1 | 102 | officer |
| 1 | 103 | member |
| 1 | 104 | member |
| 2 | 105 | leader |
| 2 | 106 | member |
| 2 | 107 | member |
| 3 | 108 | leader |
| 3 | 109 | member |
Player
| player_id | gamertag | last_match_on |
|---|---|---|
| 101 | ShadowFang | 2025-06-30 |
| 102 | PixelPriya | 2025-06-24 |
| 103 | LagLord | 2025-06-23 |
| 104 | FrostDev | 2025-06-27 |
| 105 | KingKabir | 2025-06-29 |
| 106 | ClutchDiya | 2025-06-25 |
| 107 | AceAisha | NULL |
| 108 | BotSlayer | 2025-06-28 |
| 109 | NovaNoah | 2025-06-26 |
Output
| guild_name | members | active_members |
|---|---|---|
| Rajput Raiders | 4 | 3 |
How to solve Guilds With Three Active Members in the Last Week
Join the three tables so every membership row has the player's last_match_on, then group by guild. COUNT(*) is the guild size; SUM(CASE WHEN last_match_on BETWEEN '2025-06-24' AND '2025-06-30' THEN 1 ELSE 0 END) counts the active members in the same pass — conditional aggregation.
The window is seven days including the report day, so it begins on the 24th: LagLord's match on the 23rd is one day too old. DATEDIFF('2025-06-30', last_match_on) BETWEEN 0 AND 6 is the same test written as an age. A NULL date fails both forms, so a member who never played counts towards members but not towards active_members.
Filtering inactive players in WHERE would be wrong here: it would also shrink members. The threshold is on the aggregate, so it goes in HAVING (or in an outer WHERE over a grouped subquery, as the alternative does). One join and one grouping.
Reference solution (MySQL)
SELECT g.guild_name,
COUNT(*) AS members,
SUM(CASE WHEN p.last_match_on BETWEEN '2025-06-24' AND '2025-06-30' THEN 1 ELSE 0 END) AS active_members
FROM Guild g
JOIN GuildMember gm ON gm.guild_id = g.guild_id
JOIN Player p ON p.player_id = gm.player_id
GROUP BY g.guild_id, g.guild_name
HAVING SUM(CASE WHEN p.last_match_on BETWEEN '2025-06-24' AND '2025-06-30' THEN 1 ELSE 0 END) >= 3
ORDER BY active_members DESC, g.guild_nameAnother way
SELECT g.guild_name, m.members, m.active_members
FROM Guild g
JOIN (SELECT gm.guild_id, COUNT(*) AS members,
COUNT(CASE WHEN DATEDIFF('2025-06-30', p.last_match_on) BETWEEN 0 AND 6 THEN 1 END) AS active_members
FROM GuildMember gm JOIN Player p ON p.player_id = gm.player_id
GROUP BY gm.guild_id) m ON m.guild_id = g.guild_id
WHERE m.active_members >= 3
ORDER BY m.active_members DESC, g.guild_name← Whale Share of Monthly Store Revenue · Email Domains Behind Unverified Sign-ups →