Zomato: Live Menu Rebuilt From the Latest Approved Edits
Restaurant partners edit their Zomato menus from the partner app, and every edit goes to a review queue.
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Dates
- Dialect: MySQL
- Problem: #1041
Problem statement
Restaurant partners edit their Zomato menus from the partner app, and every edit goes to a review queue. The menu service keeps only the edit log, so the catalogue team rebuilds the live menu from it.
An item is identified by restaurant_id and item_name, and every edit is a full snapshot of the item. An item's live edit is its latest approved edit — latest by edited_at, and for two edits at the same moment the higher edit_id. The item is on the live menu when it has a live edit and that edit is not a deletion (is_deleted = 0). For every live item return restaurant_id, item_name, live_price (the live edit's price) and pending_changes — the number of the item's pending edits made after its live edit (later edited_at, or the same edited_at with a higher edit_id). Rejected edits count for nothing. Order by restaurant_id, then item_name.
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: MenuEdit
| Column | Type |
|---|---|
| edit_id | int |
| restaurant_id | int |
| item_name | varchar |
| price | int |
| is_deleted | bool |
| status | enum(approved, pending, rejected) |
| edited_at | datetime |
Primary key: edit_id.
One row per menu edit submitted by a partner; price in rupees. is_deleted is 1 for an edit that removes the item. Item names are always spelled the same way.
Examples
Example 1
MenuEdit
| edit_id | restaurant_id | item_name | price | is_deleted | status | edited_at |
|---|---|---|---|---|---|---|
| 1 | 1 | Paneer Tikka | 260 | 0 | approved | 2024-09-01 10:00:00 |
| 2 | 1 | Paneer Tikka | 280 | 0 | approved | 2024-09-10 10:00:00 |
| 3 | 1 | Paneer Tikka | 300 | 0 | pending | 2024-09-12 09:00:00 |
| 4 | 1 | Paneer Tikka | 250 | 0 | rejected | 2024-09-11 09:00:00 |
| 5 | 1 | Garlic Naan | 60 | 0 | approved | 2024-09-01 10:00:00 |
| 6 | 1 | Garlic Naan | 60 | 1 | approved | 2024-09-05 18:00:00 |
| 7 | 1 | Mango Lassi | 120 | 0 | pending | 2024-09-02 12:00:00 |
| 8 | 2 | Masala Dosa | 110 | 0 | approved | 2024-09-03 08:00:00 |
| 9 | 2 | Masala Dosa | 120 | 0 | approved | 2024-09-03 08:00:00 |
| 10 | 2 | Masala Dosa | 130 | 0 | pending | 2024-09-03 08:00:00 |
| 11 | 2 | Masala Dosa | 140 | 0 | pending | 2024-09-01 08:00:00 |
| 12 | 2 | Filter Coffee | 40 | 1 | approved | 2024-08-20 07:00:00 |
| 13 | 2 | Filter Coffee | 45 | 0 | approved | 2024-08-25 07:00:00 |
Output
| restaurant_id | item_name | live_price | pending_changes |
|---|---|---|---|
| 1 | Paneer Tikka | 280 | 1 |
| 2 | Filter Coffee | 45 | 0 |
| 2 | Masala Dosa | 120 | 1 |
How to solve Zomato: Live Menu Rebuilt From the Latest Approved Edits
An append-only edit log is turned into current state with keep-the-latest-row deduplication. Restrict to approved edits, number each item's edits from the newest with ROW_NUMBER() OVER (PARTITION BY restaurant_id, item_name ORDER BY edited_at DESC, edit_id DESC), and row 1 is the live edit. The edit_id tie-break is not decoration: two edits approved at the same moment would otherwise leave the choice — and the price — to the storage order.
The order of the steps matters. A deletion is an approved edit like any other, so it must take part in choosing the latest edit; only then are items whose live edit is a deletion dropped (is_deleted = 0 on row 1). Filtering deletions out first would make an earlier approved price look current and bring a removed dish back. An item with only pending or rejected edits has no approved row at all and is not on the menu.
pending_changes is a correlated count of the item's pending edits that come after the live edit in the same (time, id) order — the queue the reviewers still have to clear for it. Rejected edits appear in neither step.
The alternative finds the live edit without window functions: an approved edit for which no later approved edit of the same item exists (NOT EXISTS), then counts later pending edits with a LEFT JOIN and COUNT(p.edit_id), which is 0 when nothing joins. The window version sorts each item's edits once; the anti-join version wants an index on (restaurant_id, item_name, status, edited_at).
Reference solution (MySQL)
WITH approved AS (
SELECT edit_id, restaurant_id, item_name, price, is_deleted, edited_at,
ROW_NUMBER() OVER (PARTITION BY restaurant_id, item_name ORDER BY edited_at DESC, edit_id DESC) AS rn
FROM MenuEdit
WHERE status = 'approved'
)
SELECT a.restaurant_id, a.item_name, a.price AS live_price,
(SELECT COUNT(*) FROM MenuEdit p
WHERE p.restaurant_id = a.restaurant_id AND p.item_name = a.item_name
AND p.status = 'pending'
AND (p.edited_at > a.edited_at OR (p.edited_at = a.edited_at AND p.edit_id > a.edit_id))) AS pending_changes
FROM approved a
WHERE a.rn = 1 AND a.is_deleted = 0
ORDER BY a.restaurant_id, a.item_nameAnother way
SELECT a.restaurant_id, a.item_name, a.price AS live_price,
COUNT(p.edit_id) AS pending_changes
FROM MenuEdit a
LEFT JOIN MenuEdit p
ON p.restaurant_id = a.restaurant_id AND p.item_name = a.item_name
AND p.status = 'pending'
AND (p.edited_at > a.edited_at OR (p.edited_at = a.edited_at AND p.edit_id > a.edit_id))
WHERE a.status = 'approved' AND a.is_deleted = 0
AND NOT EXISTS (
SELECT 1 FROM MenuEdit b
WHERE b.restaurant_id = a.restaurant_id AND b.item_name = a.item_name AND b.status = 'approved'
AND (b.edited_at > a.edited_at OR (b.edited_at = a.edited_at AND b.edit_id > a.edit_id))
)
GROUP BY a.edit_id, a.restaurant_id, a.item_name, a.price
ORDER BY a.restaurant_id, a.item_name← Zomato: September Photo Decisions of Every Moderator · Zomato: Cuisine Gaps in Each Locality Worth Recruiting For →