Meta: Moderation Reports Reviewed Past Their SLA
Meta's content-moderation queue gives every report a priority, and each priority has a service-level target: the maximum number of hours a report may wait…
- Difficulty: Easy
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #754
Problem statement
Meta's content-moderation queue gives every report a priority, and each priority has a service-level target: the maximum number of hours a report may wait for a reviewer's decision.
Return every reviewed report whose review took longer than its priority's max_hours, measured from created_at to reviewed_at in whole minutes. A review finished exactly at the limit is on time, and reports not yet reviewed (reviewed_at NULL) are not part of this list. Return the columns report_id, priority and minutes_to_review, 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: SlaPolicy
| Column | Type |
|---|---|
| priority | enum(P0, P1, P2) |
| max_hours | int |
Primary key: priority.
The review-time target of each priority.
Table: ModReport
| Column | Type |
|---|---|
| report_id | int |
| content_id | int |
| priority | enum(P0, P1, P2) |
| created_at | datetime |
| reviewed_at | datetime |
Primary key: report_id.
One row per report in the queue. Every priority used here is in SlaPolicy; times are recorded to the minute.
Examples
Example 1
SlaPolicy
| priority | max_hours |
|---|---|
| P0 | 1 |
| P1 | 4 |
| P2 | 24 |
ModReport
| report_id | content_id | priority | created_at | reviewed_at |
|---|---|---|---|---|
| 1 | 9001 | P0 | 2025-04-10 10:00:00 | 2025-04-10 10:45:00 |
| 2 | 9002 | P0 | 2025-04-10 11:00:00 | 2025-04-10 12:30:00 |
| 3 | 9003 | P1 | 2025-04-10 09:00:00 | 2025-04-10 13:00:00 |
| 4 | 9004 | P1 | 2025-04-10 09:00:00 | NULL |
| 5 | 9005 | P2 | 2025-04-09 08:00:00 | 2025-04-10 09:15:00 |
| 6 | 9006 | P2 | 2025-04-09 20:00:00 | 2025-04-10 06:00:00 |
| 7 | 9007 | P1 | 2025-04-11 23:30:00 | 2025-04-12 03:31:00 |
Output
| report_id | priority | minutes_to_review |
|---|---|---|
| 2 | P0 | 90 |
| 5 | P2 | 1515 |
| 7 | P1 | 241 |
How to solve Meta: Moderation Reports Reviewed Past Their SLA
Every report has to be checked against the target of its own priority, so the query joins ModReport to SlaPolicy on priority; after that each row carries both its timestamps and its limit, and the rest is a filter.
The waiting time is TIMESTAMPDIFF(MINUTE, created_at, reviewed_at) and the limit in the same unit is max_hours * 60. Late means strictly more, so a review that lands exactly on the limit (240 minutes for a 4-hour P1) is on time. An unreviewed report has a NULL reviewed_at, which makes the difference NULL and the comparison unknown, so it drops out; the explicit IS NOT NULL documents that.
The first alternative builds a deadline instead — DATE_ADD(created_at, INTERVAL max_hours HOUR) — and compares timestamps; the second looks the limit up with a correlated scalar subquery rather than a join. SlaPolicy has three rows, so every version is one pass over the reports.
Reference solution (MySQL)
SELECT r.report_id, r.priority, TIMESTAMPDIFF(MINUTE, r.created_at, r.reviewed_at) AS minutes_to_review
FROM ModReport r
JOIN SlaPolicy s ON s.priority = r.priority
WHERE r.reviewed_at IS NOT NULL
AND TIMESTAMPDIFF(MINUTE, r.created_at, r.reviewed_at) > s.max_hours * 60Another way
SELECT r.report_id, r.priority, TIMESTAMPDIFF(MINUTE, r.created_at, r.reviewed_at) AS minutes_to_review FROM ModReport r JOIN SlaPolicy s ON s.priority = r.priority WHERE r.reviewed_at > DATE_ADD(r.created_at, INTERVAL s.max_hours HOUR)Another way
SELECT report_id, priority, TIMESTAMPDIFF(MINUTE, created_at, reviewed_at) AS minutes_to_review FROM ModReport r WHERE TIMESTAMPDIFF(MINUTE, created_at, reviewed_at) > 60 * (SELECT max_hours FROM SlaPolicy s WHERE s.priority = r.priority)← Meta: Messenger Video Call Minutes by Day of the Week · Meta: Quest Store Apps Rated by at Least Three Players →