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
| Column | Type |
|---|---|
| moderator_id | int |
| moderator_name | varchar |
| is_active | bool |
Primary key: moderator_id.
One row per member of the photo-moderation team; is_active is 0 for someone who has left.
Table: PhotoDecision
| Column | Type |
|---|---|
| decision_id | int |
| moderator_id | int |
| photo_id | int |
| decision | enum(approved, rejected) |
| decided_at | datetime |
Primary key: decision_id.
One row per moderation decision on an uploaded photo.
Examples
Example 1
Moderator
| moderator_id | moderator_name | is_active |
|---|---|---|
| 1 | Aisha | 1 |
| 2 | Farhan | 1 |
| 3 | Pooja | 1 |
| 4 | Karan | 0 |
PhotoDecision
| decision_id | moderator_id | photo_id | decision | decided_at |
|---|---|---|---|---|
| 1 | 1 | 501 | approved | 2024-09-01 00:00:00 |
| 2 | 1 | 502 | rejected | 2024-09-14 16:20:00 |
| 3 | 1 | 503 | approved | 2024-09-30 23:59:00 |
| 4 | 1 | 504 | approved | 2024-10-01 00:00:00 |
| 5 | 2 | 505 | approved | 2024-08-31 22:10:00 |
| 6 | 3 | 506 | rejected | 2024-09-05 11:00:00 |
| 7 | 4 | 507 | approved | 2024-09-06 09:30:00 |
Output
| moderator_id | moderator_name | approved | rejected | last_decision_at |
|---|---|---|---|---|
| 1 | Aisha | 2 | 1 | 2024-09-30 23:59:00 |
| 2 | Farhan | 0 | 0 | NULL |
| 3 | Pooja | 0 | 1 | 2024-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_idAnother 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_idAnother 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 →