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
| Column | Type |
|---|---|
| report_id | int |
| reported_player_id | int |
| reporter_player_id | int |
| reason | enum(aimbot, wallhack, speed_hack, griefing) |
| reported_at | datetime |
Primary key: report_id.
One row per report filed. A reporter never reports themselves.
Examples
Example 1
CheatReport
| report_id | reported_player_id | reporter_player_id | reason | reported_at |
|---|---|---|---|---|
| 1 | 7 | 11 | aimbot | 2025-03-02 22:10:00 |
| 2 | 7 | 12 | wallhack | 2025-03-15 21:00:00 |
| 3 | 7 | 13 | aimbot | 2025-03-31 23:59:00 |
| 4 | 7 | 14 | aimbot | 2025-04-01 00:05:00 |
| 5 | 8 | 11 | speed_hack | 2025-03-05 18:00:00 |
| 6 | 8 | 11 | speed_hack | 2025-03-06 18:00:00 |
| 7 | 8 | 11 | aimbot | 2025-03-07 18:00:00 |
| 8 | 8 | 15 | griefing | 2025-03-07 19:00:00 |
| 9 | 8 | 16 | wallhack | 2025-03-08 10:00:00 |
Output
| reported_player_id | report_month | distinct_reporters |
|---|---|---|
| 7 | 2025-03 | 3 |
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_idAnother 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_idAnother 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 →