Meta: Moderators Overturned More Often Than Their Team

Medium
761.

Meta: Moderators Overturned More Often Than Their Team

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

ColumnType
moderator_idint
namevarchar
teamvarchar

Primary key: moderator_id.

One row per reviewer; team is the policy area they review.

Table: ModDecision

ColumnType
decision_idint
moderator_idint
decided_ondate
overturnedbool

Primary key: decision_id.

One row per enforcement decision; overturned is 1 when an appeal reversed it.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Moderator

moderator_idnameteam
1AditiHate Speech
2RohanHate Speech
3KavyaSpam
4FarhanSpam
5NehaSpam
6DevNudity
7IraSpam

ModDecision

decision_idmoderator_iddecided_onoverturned
112025-05-011
212025-05-011
312025-05-020
422025-05-020
522025-05-030
632025-05-011
732025-05-020
842025-05-020
942025-05-031
1062025-05-011
2 more rows

Output

moderator_idnamedecisionsoverturned
1Aditi32
7Ira11
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.

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

ColumnType
moderator_idint
namevarchar
teamvarchar

Primary key: moderator_id.

One row per reviewer; team is the policy area they review.

Table: ModDecision

ColumnType
decision_idint
moderator_idint
decided_ondate
overturnedbool

Primary key: decision_id.

One row per enforcement decision; overturned is 1 when an appeal reversed it.

Examples

Example 1

Moderator

moderator_idnameteam
1AditiHate Speech
2RohanHate Speech
3KavyaSpam
4FarhanSpam
5NehaSpam
6DevNudity
7IraSpam

ModDecision

decision_idmoderator_iddecided_onoverturned
112025-05-011
212025-05-011
312025-05-020
422025-05-020
522025-05-030
632025-05-011
732025-05-020
842025-05-020
942025-05-031
1062025-05-011
1162025-05-030
1272025-05-031

Output

moderator_idnamedecisionsoverturned
1Aditi32
7Ira11

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.decisions

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