Meta: Moderators Overturned More Often Than Their Team
Content-moderation quality leads look at how often a reviewer's decisions are overturned on appeal.
- Difficulty: Medium
- Topics: Aggregation, Subqueries, Joins
- Dialect: MySQL
- Problem: #761
Problem statement
Content-moderation quality leads look at how often a reviewer's decisions are overturned on appeal. A moderator's overturn rate is overturned decisions ÷ all their decisions; a team's rate is the same over every decision made by the team's members.
Return the moderators whose overturn rate is strictly higher than their team's rate, with the columns moderator_id, name, decisions and overturned. Moderators with no decisions are not in the answer (and add nothing to their team). Return the rows in any order.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: Moderator
| Column | Type |
|---|---|
| moderator_id | int |
| name | varchar |
| team | varchar |
Primary key: moderator_id.
One row per reviewer; team is the policy area they review.
Table: ModDecision
| Column | Type |
|---|---|
| decision_id | int |
| moderator_id | int |
| decided_on | date |
| overturned | bool |
Primary key: decision_id.
One row per enforcement decision; overturned is 1 when an appeal reversed it.
Examples
Example 1
Moderator
| moderator_id | name | team |
|---|---|---|
| 1 | Aditi | Hate Speech |
| 2 | Rohan | Hate Speech |
| 3 | Kavya | Spam |
| 4 | Farhan | Spam |
| 5 | Neha | Spam |
| 6 | Dev | Nudity |
| 7 | Ira | Spam |
ModDecision
| decision_id | moderator_id | decided_on | overturned |
|---|---|---|---|
| 1 | 1 | 2025-05-01 | 1 |
| 2 | 1 | 2025-05-01 | 1 |
| 3 | 1 | 2025-05-02 | 0 |
| 4 | 2 | 2025-05-02 | 0 |
| 5 | 2 | 2025-05-03 | 0 |
| 6 | 3 | 2025-05-01 | 1 |
| 7 | 3 | 2025-05-02 | 0 |
| 8 | 4 | 2025-05-02 | 0 |
| 9 | 4 | 2025-05-03 | 1 |
| 10 | 6 | 2025-05-01 | 1 |
| 11 | 6 | 2025-05-03 | 0 |
| 12 | 7 | 2025-05-03 | 1 |
Output
| moderator_id | name | decisions | overturned |
|---|---|---|---|
| 1 | Aditi | 3 | 2 |
| 7 | Ira | 1 | 1 |
How to solve Meta: Moderators Overturned More Often Than Their Team
The comparison is between two levels of aggregation: a moderator and the team they belong to. Build the moderator level first — decisions and overturned per moderator, with an inner join so a moderator with no decisions never appears — and then the team level from those rows, which also guarantees that idle moderators add nothing to their team. Joining the two on team puts every moderator next to their team's totals.
To compare the two rates without division, cross-multiply: overturned / decisions > team_overturned / team_decisions becomes overturned * team_decisions > team_overturned * decisions, valid because both denominators are positive. Integers compare exactly, so a moderator whose rate equals the team's — always the case for a one-person team — is correctly left out.
The alternative computes the team totals with window sums over the grouped rows (SUM(COUNT(*)) OVER (PARTITION BY team)) and compares the divided rates. Both group the decisions once.
Reference solution (MySQL)
WITH per_mod AS (
SELECT m.moderator_id, m.name, m.team, COUNT(*) AS decisions, SUM(d.overturned) AS overturned
FROM Moderator m
JOIN ModDecision d ON d.moderator_id = m.moderator_id
GROUP BY m.moderator_id, m.name, m.team
), per_team AS (
SELECT team, SUM(decisions) AS team_decisions, SUM(overturned) AS team_overturned
FROM per_mod GROUP BY team
)
SELECT p.moderator_id, p.name, p.decisions, p.overturned
FROM per_mod p
JOIN per_team t ON t.team = p.team
WHERE p.overturned * t.team_decisions > t.team_overturned * p.decisionsAnother way
SELECT moderator_id, name, decisions, overturned
FROM (SELECT m.moderator_id, m.name, COUNT(*) AS decisions, SUM(d.overturned) AS overturned,
SUM(SUM(d.overturned)) OVER (PARTITION BY m.team) AS team_overturned,
SUM(COUNT(*)) OVER (PARTITION BY m.team) AS team_decisions
FROM Moderator m JOIN ModDecision d ON d.moderator_id = m.moderator_id
GROUP BY m.moderator_id, m.name, m.team) x
WHERE overturned / decisions > team_overturned / team_decisions← Meta: Advertisers Whose Spend Fell Three Months Running · Meta: Instagram Follow-Backs Within a Week →