Players Reported for Cheating by Three Reporters in a Month

Medium
184.

Players Reported for Cheating by Three Reporters in a Month

Players can report each other from the end-of-match screen. Reports for aimbot, wallhack or speed_hack are cheating reports; griefing is a behaviour report and does not count here. The anti-cheat team reviews a player for a calendar month when at least 3 different reporters filed cheating reports against them in that month — one angry player filing five reports is still one reporter.

Return reported_player_id, report_month (as YYYY-MM) and distinct_reporters, ordered by report_month, then reported_player_id.

Tables

Table: CheatReport

ColumnTypeValues
report_idint
reported_player_idint
reporter_player_idint
reasonenumaimbot, wallhack, speed_hack, griefing
reported_atdatetime

Primary key: report_id.

One row per report filed. A reporter never reports themselves.

Example

The rows must come back in the order shown.

CheatReport

report_idreported_player_idreporter_player_idreasonreported_at
1711aimbot2025-03-02 22:10:00
2712wallhack2025-03-15 21:00:00
3713aimbot2025-03-31 23:59:00
4714aimbot2025-04-01 00:05:00
5811speed_hack2025-03-05 18:00:00
6811speed_hack2025-03-06 18:00:00
7811aimbot2025-03-07 18:00:00
8815griefing2025-03-07 19:00:00
9816wallhack2025-03-08 10:00:00

Output

reported_player_idreport_monthdistinct_reporters
72025-033
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 Reported for Cheating by Three Reporters in a Month

Players can report each other from the end-of-match screen. Reports for aimbot, wallhack or speed_hack are cheating reports; griefing is a behaviour report…

  • Difficulty: Medium
  • Topics: Aggregation, Dates, Strings
  • Dialect: MySQL
  • Problem: #184

Problem statement

Players can report each other from the end-of-match screen. Reports for aimbot, wallhack or speed_hack are cheating reports; griefing is a behaviour report and does not count here. The anti-cheat team reviews a player for a calendar month when at least 3 different reporters filed cheating reports against them in that month — one angry player filing five reports is still one reporter.

Return reported_player_id, report_month (as YYYY-MM) and distinct_reporters, ordered by report_month, then reported_player_id.

Tables

Table: CheatReport

ColumnType
report_idint
reported_player_idint
reporter_player_idint
reasonenum(aimbot, wallhack, speed_hack, griefing)
reported_atdatetime

Primary key: report_id.

One row per report filed. A reporter never reports themselves.

Examples

Example 1

CheatReport

report_idreported_player_idreporter_player_idreasonreported_at
1711aimbot2025-03-02 22:10:00
2712wallhack2025-03-15 21:00:00
3713aimbot2025-03-31 23:59:00
4714aimbot2025-04-01 00:05:00
5811speed_hack2025-03-05 18:00:00
6811speed_hack2025-03-06 18:00:00
7811aimbot2025-03-07 18:00:00
8815griefing2025-03-07 19:00:00
9816wallhack2025-03-08 10:00:00

Output

reported_player_idreport_monthdistinct_reporters
72025-033

How to solve Players Reported for Cheating by Three Reporters in a Month

Filter to the cheating reasons, then group by the pair (reported player, month). The month is a string key built from the timestamp: DATE_FORMAT(reported_at, '%Y-%m'), LEFT(reported_at, 7) or a CONCAT of YEAR and a zero-padded MONTH all give 2025-03. A report at 23:59 on 31 March and one five minutes into April land in different months, so player 7 has three reporters in March and only one in April.

Within a group COUNT(DISTINCT reporter_player_id) counts reporters rather than reports — player 8 got four cheating reports in March, three of them from the same person, so only two reporters count and the griefing report does not count at all. HAVING keeps the groups with three or more.

Taking the DISTINCT triples first and then COUNT(*) per group is the same computation in two steps. It is one scan plus a grouping.

Reference solution (MySQL)

SELECT reported_player_id,
       DATE_FORMAT(reported_at, '%Y-%m') AS report_month,
       COUNT(DISTINCT reporter_player_id) AS distinct_reporters
FROM CheatReport
WHERE reason IN ('aimbot', 'wallhack', 'speed_hack')
GROUP BY reported_player_id, DATE_FORMAT(reported_at, '%Y-%m')
HAVING COUNT(DISTINCT reporter_player_id) >= 3
ORDER BY report_month, reported_player_id

Another way

SELECT reported_player_id, report_month, COUNT(*) AS distinct_reporters
FROM (SELECT DISTINCT reported_player_id, LEFT(reported_at, 7) AS report_month, reporter_player_id
      FROM CheatReport WHERE reason <> 'griefing') d
GROUP BY reported_player_id, report_month
HAVING COUNT(*) >= 3
ORDER BY report_month, reported_player_id

Another way

SELECT reported_player_id, CONCAT(YEAR(reported_at), '-', LPAD(MONTH(reported_at), 2, '0')) AS report_month,
       COUNT(DISTINCT reporter_player_id) AS distinct_reporters
FROM CheatReport WHERE reason <> 'griefing'
GROUP BY reported_player_id, report_month
HAVING COUNT(DISTINCT reporter_player_id) >= 3
ORDER BY report_month, reported_player_id

← Ranked Queue Wait Time by Hour of Day · Ranked Tier Demotions Between Weekly Snapshots →