Moderation Outcomes by Report Reason — SQL Problem
The trust-and-safety lead wants one table summarising the report queue by reason.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #434
Problem statement
The trust-and-safety lead wants one table summarising the report queue by reason. A report is pending until a moderator reviews it, after which it is actioned (content removed) or dismissed.
Return the columns reason, total_reports, actioned, dismissed, pending and action_rate — the percentage of reviewed reports (actioned + dismissed) that were actioned, rounded to 1 decimal, or NULL when none of the reason's reports has been reviewed. Order by total_reports descending, then reason.
Tables
Table: reports
| Column | Type |
|---|---|
| report_id | int |
| post_id | int |
| reason | varchar |
| status | enum(pending, actioned, dismissed) |
| reported_at | datetime |
Primary key: report_id.
One row per user report. reason is one of a fixed list of lower-case codes.
Examples
Example 1
reports
| report_id | post_id | reason | status | reported_at |
|---|---|---|---|---|
| 1 | 101 | spam | actioned | 2025-04-01 10:00:00 |
| 2 | 102 | spam | actioned | 2025-04-01 11:30:00 |
| 3 | 103 | spam | dismissed | 2025-04-02 09:10:00 |
| 4 | 104 | harassment | pending | 2025-04-02 14:00:00 |
| 5 | 105 | harassment | actioned | 2025-04-03 08:45:00 |
| 6 | 106 | impersonation | pending | 2025-04-03 20:20:00 |
| 7 | 107 | spam | pending | 2025-04-04 07:00:00 |
| 8 | 108 | harassment | dismissed | 2025-04-04 16:40:00 |
Output
| reason | total_reports | actioned | dismissed | pending | action_rate |
|---|---|---|---|---|---|
| spam | 4 | 2 | 1 | 1 | 66.7 |
| harassment | 3 | 1 | 1 | 1 | 50 |
| impersonation | 1 | 0 | 0 | 1 | NULL |
How to solve Moderation Outcomes by Report Reason
This is conditional aggregation: one pass over the reports grouped by reason, where each output column is an aggregate over a CASE. SUM(CASE WHEN status = 'actioned' THEN 1 ELSE 0 END) counts actioned reports; the same shape counts dismissed and pending ones. COUNT(IF(cond, 1, NULL)) is an equivalent spelling, since COUNT skips NULLs.
The action rate is over reviewed reports only, so its denominator is actioned + dismissed. For a reason whose reports are all pending that denominator is 0; NULLIF(…, 0) turns it into NULL so the rate is NULL instead of an error. The alternative gets the rate from an AVG over a CASE that yields 100 for actioned, 0 for dismissed and NULL for pending — AVG ignores the NULLs, and when every value is NULL it returns NULL, which is exactly the rule.
Rounding to one decimal keeps MySQL's four-digit decimal division and other engines in agreement. Ties on the total fall back to the reason code.
Reference solution (MySQL)
SELECT reason,
COUNT(*) AS total_reports,
SUM(CASE WHEN status = 'actioned' THEN 1 ELSE 0 END) AS actioned,
SUM(CASE WHEN status = 'dismissed' THEN 1 ELSE 0 END) AS dismissed,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
ROUND(SUM(CASE WHEN status = 'actioned' THEN 1 ELSE 0 END) * 100
/ NULLIF(SUM(CASE WHEN status <> 'pending' THEN 1 ELSE 0 END), 0), 1) AS action_rate
FROM reports
GROUP BY reason
ORDER BY total_reports DESC, reasonAnother way
SELECT reason, COUNT(*) AS total_reports,
COUNT(IF(status = 'actioned', 1, NULL)) AS actioned,
COUNT(IF(status = 'dismissed', 1, NULL)) AS dismissed,
COUNT(IF(status = 'pending', 1, NULL)) AS pending,
ROUND(AVG(CASE WHEN status = 'actioned' THEN 100 WHEN status = 'dismissed' THEN 0 END), 1) AS action_rate
FROM reports GROUP BY reason ORDER BY total_reports DESC, reason← Each Creator's Most-Liked Post · Drive-By Commenters Who Never Liked the Post →