Guilds With Three Active Members in the Last Week

Medium
187.

Guilds With Three Active Members in the Last Week

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

ColumnType
guild_idint
guild_namevarchar

Primary key: guild_id.

Guild names are unique.

Table: GuildMember

ColumnTypeValues
guild_idint
player_idint
roleenumleader, officer, member

Primary key: player_id.

A player belongs to at most one guild.

Table: Player

ColumnType
player_idint
gamertagvarchar
last_match_ondate

Primary key: player_id.

last_match_on is the day of the player's latest finished match (never after 30 June 2025), or NULL.

Example

The rows must come back in the order shown.

Guild

guild_idguild_name
1Rajput Raiders
2Midnight Snipers
3Chai Squad

GuildMember

guild_idplayer_idrole
1101leader
1102officer
1103member
1104member
2105leader
2106member
2107member
3108leader
3109member

Player

player_idgamertaglast_match_on
101ShadowFang2025-06-30
102PixelPriya2025-06-24
103LagLord2025-06-23
104FrostDev2025-06-27
105KingKabir2025-06-29
106ClutchDiya2025-06-25
107AceAishaNULL
108BotSlayer2025-06-28
109NovaNoah2025-06-26

Output

guild_namemembersactive_members
Rajput Raiders43
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.

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

ColumnType
guild_idint
guild_namevarchar

Primary key: guild_id.

Guild names are unique.

Table: GuildMember

ColumnType
guild_idint
player_idint
roleenum(leader, officer, member)

Primary key: player_id.

A player belongs to at most one guild.

Table: Player

ColumnType
player_idint
gamertagvarchar
last_match_ondate

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_idguild_name
1Rajput Raiders
2Midnight Snipers
3Chai Squad

GuildMember

guild_idplayer_idrole
1101leader
1102officer
1103member
1104member
2105leader
2106member
2107member
3108leader
3109member

Player

player_idgamertaglast_match_on
101ShadowFang2025-06-30
102PixelPriya2025-06-24
103LagLord2025-06-23
104FrostDev2025-06-27
105KingKabir2025-06-29
106ClutchDiya2025-06-25
107AceAishaNULL
108BotSlayer2025-06-28
109NovaNoah2025-06-26

Output

guild_namemembersactive_members
Rajput Raiders43

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_name

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