Zomato: September Photo Decisions of Every Moderator

Medium
1040.

Zomato: September Photo Decisions of Every Moderator

Diners upload food and ambience photos to restaurant pages, and Zomato's content-moderation team approves or rejects each one. The team lead's monthly report must list every active moderator — including anyone who decided nothing that month.

For every moderator with is_active = 1, count their photo decisions made in September 2024 (decided_at from 2024-09-01 00:00:00 up to, not including, 2024-10-01 00:00:00): approved, rejected, and last_decision_at — the time of their latest September decision, NULL if they made none. Decisions in other months do not count. Return moderator_id, moderator_name, approved, rejected and last_decision_at, ordered by moderator_id.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: Moderator

ColumnType
moderator_idint
moderator_namevarchar
is_activebool

Primary key: moderator_id.

One row per member of the photo-moderation team; is_active is 0 for someone who has left.

Table: PhotoDecision

ColumnTypeValues
decision_idint
moderator_idint
photo_idint
decisionenumapproved, rejected
decided_atdatetime

Primary key: decision_id.

One row per moderation decision on an uploaded photo.

Example

The rows must come back in the order shown.

Moderator

moderator_idmoderator_nameis_active
1Aisha1
2Farhan1
3Pooja1
4Karan0

PhotoDecision

decision_idmoderator_idphoto_iddecisiondecided_at
11501approved2024-09-01 00:00:00
21502rejected2024-09-14 16:20:00
31503approved2024-09-30 23:59:00
41504approved2024-10-01 00:00:00
52505approved2024-08-31 22:10:00
63506rejected2024-09-05 11:00:00
74507approved2024-09-06 09:30:00

Output

moderator_idmoderator_nameapprovedrejectedlast_decision_at
1Aisha212024-09-30 23:59:00
2Farhan00NULL
3Pooja012024-09-05 11:00:00
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.

Zomato: September Photo Decisions of Every Moderator

Diners upload food and ambience photos to restaurant pages, and Zomato's content-moderation team approves or rejects each one.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Conditional Logic
  • Dialect: MySQL
  • Problem: #1040

Problem statement

Diners upload food and ambience photos to restaurant pages, and Zomato's content-moderation team approves or rejects each one. The team lead's monthly report must list every active moderator — including anyone who decided nothing that month.

For every moderator with is_active = 1, count their photo decisions made in September 2024 (decided_at from 2024-09-01 00:00:00 up to, not including, 2024-10-01 00:00:00): approved, rejected, and last_decision_at — the time of their latest September decision, NULL if they made none. Decisions in other months do not count. Return moderator_id, moderator_name, approved, rejected and last_decision_at, ordered by moderator_id.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: Moderator

ColumnType
moderator_idint
moderator_namevarchar
is_activebool

Primary key: moderator_id.

One row per member of the photo-moderation team; is_active is 0 for someone who has left.

Table: PhotoDecision

ColumnType
decision_idint
moderator_idint
photo_idint
decisionenum(approved, rejected)
decided_atdatetime

Primary key: decision_id.

One row per moderation decision on an uploaded photo.

Examples

Example 1

Moderator

moderator_idmoderator_nameis_active
1Aisha1
2Farhan1
3Pooja1
4Karan0

PhotoDecision

decision_idmoderator_idphoto_iddecisiondecided_at
11501approved2024-09-01 00:00:00
21502rejected2024-09-14 16:20:00
31503approved2024-09-30 23:59:00
41504approved2024-10-01 00:00:00
52505approved2024-08-31 22:10:00
63506rejected2024-09-05 11:00:00
74507approved2024-09-06 09:30:00

Output

moderator_idmoderator_nameapprovedrejectedlast_decision_at
1Aisha212024-09-30 23:59:00
2Farhan00NULL
3Pooja012024-09-05 11:00:00

How to solve Zomato: September Photo Decisions of Every Moderator

The report is about every active moderator, so it starts from Moderator and outer-joins the decisions. The whole problem is where each condition goes.

The month filter is a condition on the child rows. Placed in the LEFT JOIN … ON, it decides which decisions attach to each moderator; a moderator with no September decision still produces one row, padded with NULLs. Placed in WHERE instead, d.decided_at >= '2024-09-01' is evaluated on that padded row, is NULL, and removes it — the left join silently turns into an inner join and the idle moderator, the very person the lead wants to see, vanishes. The same happens to someone whose only decisions were in August.

The is_active test is a condition on the parent, and it belongs in WHERE: putting it in ON would not remove anyone, it would only stop inactive moderators' decisions from joining while still listing them with zeros.

After grouping, COUNT(CASE WHEN d.decision = 'approved' THEN 1 END) counts only real approvals (0 for a padded row), and MAX(d.decided_at) is NULL when nothing joined. The month is half-open — from midnight on 1 September up to but excluding midnight on 1 October — so 23:59 on the 30th counts and 00:00 on 1 October does not.

The alternatives get the same rows with correlated subqueries per moderator, and by filtering the decisions in a derived table before the outer join — the other correct place for a child condition.

Reference solution (MySQL)

SELECT m.moderator_id, m.moderator_name,
       COUNT(CASE WHEN d.decision = 'approved' THEN 1 END) AS approved,
       COUNT(CASE WHEN d.decision = 'rejected' THEN 1 END) AS rejected,
       MAX(d.decided_at) AS last_decision_at
FROM Moderator m
LEFT JOIN PhotoDecision d
  ON d.moderator_id = m.moderator_id
 AND d.decided_at >= '2024-09-01 00:00:00'
 AND d.decided_at < '2024-10-01 00:00:00'
WHERE m.is_active = 1
GROUP BY m.moderator_id, m.moderator_name
ORDER BY m.moderator_id

Another way

SELECT m.moderator_id, m.moderator_name,
       (SELECT COUNT(*) FROM PhotoDecision d WHERE d.moderator_id = m.moderator_id AND d.decision = 'approved'
          AND d.decided_at >= '2024-09-01' AND d.decided_at < '2024-10-01') AS approved,
       (SELECT COUNT(*) FROM PhotoDecision d WHERE d.moderator_id = m.moderator_id AND d.decision = 'rejected'
          AND d.decided_at >= '2024-09-01' AND d.decided_at < '2024-10-01') AS rejected,
       (SELECT MAX(d.decided_at) FROM PhotoDecision d WHERE d.moderator_id = m.moderator_id
          AND d.decided_at >= '2024-09-01' AND d.decided_at < '2024-10-01') AS last_decision_at
FROM Moderator m
WHERE m.is_active = 1
ORDER BY m.moderator_id

Another way

SELECT m.moderator_id, m.moderator_name,
       COALESCE(SUM(IF(s.decision = 'approved', 1, 0)), 0) AS approved,
       COALESCE(SUM(IF(s.decision = 'rejected', 1, 0)), 0) AS rejected,
       MAX(s.decided_at) AS last_decision_at
FROM Moderator m
LEFT JOIN (SELECT moderator_id, decision, decided_at FROM PhotoDecision
           WHERE YEAR(decided_at) = 2024 AND MONTH(decided_at) = 9) s ON s.moderator_id = m.moderator_id
WHERE m.is_active = 1
GROUP BY m.moderator_id, m.moderator_name
ORDER BY m.moderator_id

← Zomato: Top Intercity Legends Dish in Each Destination City · Zomato: Live Menu Rebuilt From the Latest Approved Edits →