Moderation Outcomes by Report Reason

Medium
434.

Moderation Outcomes by Report Reason

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

ColumnTypeValues
report_idint
post_idint
reasonvarchar
statusenumpending, actioned, dismissed
reported_atdatetime

Primary key: report_id.

One row per user report. reason is one of a fixed list of lower-case codes.

Example

The rows must come back in the order shown.

reports

report_idpost_idreasonstatusreported_at
1101spamactioned2025-04-01 10:00:00
2102spamactioned2025-04-01 11:30:00
3103spamdismissed2025-04-02 09:10:00
4104harassmentpending2025-04-02 14:00:00
5105harassmentactioned2025-04-03 08:45:00
6106impersonationpending2025-04-03 20:20:00
7107spampending2025-04-04 07:00:00
8108harassmentdismissed2025-04-04 16:40:00

Output

reasontotal_reportsactioneddismissedpendingaction_rate
spam421166.7
harassment311150
impersonation1001NULL
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.

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

ColumnType
report_idint
post_idint
reasonvarchar
statusenum(pending, actioned, dismissed)
reported_atdatetime

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_idpost_idreasonstatusreported_at
1101spamactioned2025-04-01 10:00:00
2102spamactioned2025-04-01 11:30:00
3103spamdismissed2025-04-02 09:10:00
4104harassmentpending2025-04-02 14:00:00
5105harassmentactioned2025-04-03 08:45:00
6106impersonationpending2025-04-03 20:20:00
7107spampending2025-04-04 07:00:00
8108harassmentdismissed2025-04-04 16:40:00

Output

reasontotal_reportsactioneddismissedpendingaction_rate
spam421166.7
harassment311150
impersonation1001NULL

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, reason

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