Meta: Moderation Reports Reviewed Past Their SLA

Easy
754.

Meta: Moderation Reports Reviewed Past Their SLA

EasyJoinsDates

MySQL

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

ColumnTypeValues
priorityenumP0, P1, P2
max_hoursint

Primary key: priority.

The review-time target of each priority.

Table: ModReport

ColumnTypeValues
report_idint
content_idint
priorityenumP0, P1, P2
created_atdatetime
reviewed_atdatetime

Primary key: report_id.

One row per report in the queue. Every priority used here is in SlaPolicy; times are recorded to the minute.

Example

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

SlaPolicy

prioritymax_hours
P01
P14
P224

ModReport

report_idcontent_idprioritycreated_atreviewed_at
19001P02025-04-10 10:00:002025-04-10 10:45:00
29002P02025-04-10 11:00:002025-04-10 12:30:00
39003P12025-04-10 09:00:002025-04-10 13:00:00
49004P12025-04-10 09:00:00NULL
59005P22025-04-09 08:00:002025-04-10 09:15:00
69006P22025-04-09 20:00:002025-04-10 06:00:00
79007P12025-04-11 23:30:002025-04-12 03:31:00

Output

report_idpriorityminutes_to_review
2P090
5P21515
7P1241
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: 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

ColumnType
priorityenum(P0, P1, P2)
max_hoursint

Primary key: priority.

The review-time target of each priority.

Table: ModReport

ColumnType
report_idint
content_idint
priorityenum(P0, P1, P2)
created_atdatetime
reviewed_atdatetime

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

prioritymax_hours
P01
P14
P224

ModReport

report_idcontent_idprioritycreated_atreviewed_at
19001P02025-04-10 10:00:002025-04-10 10:45:00
29002P02025-04-10 11:00:002025-04-10 12:30:00
39003P12025-04-10 09:00:002025-04-10 13:00:00
49004P12025-04-10 09:00:00NULL
59005P22025-04-09 08:00:002025-04-10 09:15:00
69006P22025-04-09 20:00:002025-04-10 06:00:00
79007P12025-04-11 23:30:002025-04-12 03:31:00

Output

report_idpriorityminutes_to_review
2P090
5P21515
7P1241

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 * 60

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