Zomato: Live Menu Rebuilt From the Latest Approved Edits

Hard
1041.

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. 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

ColumnTypeValues
edit_idint
restaurant_idint
item_namevarchar
priceint
is_deletedbool
statusenumapproved, pending, rejected
edited_atdatetime

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.

Example

The rows must come back in the order shown.

MenuEdit

edit_idrestaurant_iditem_namepriceis_deletedstatusedited_at
11Paneer Tikka2600approved2024-09-01 10:00:00
21Paneer Tikka2800approved2024-09-10 10:00:00
31Paneer Tikka3000pending2024-09-12 09:00:00
41Paneer Tikka2500rejected2024-09-11 09:00:00
51Garlic Naan600approved2024-09-01 10:00:00
61Garlic Naan601approved2024-09-05 18:00:00
71Mango Lassi1200pending2024-09-02 12:00:00
82Masala Dosa1100approved2024-09-03 08:00:00
92Masala Dosa1200approved2024-09-03 08:00:00
102Masala Dosa1300pending2024-09-03 08:00:00
3 more rows

Output

restaurant_iditem_namelive_pricepending_changes
1Paneer Tikka2801
2Filter Coffee450
2Masala Dosa1201
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: 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

ColumnType
edit_idint
restaurant_idint
item_namevarchar
priceint
is_deletedbool
statusenum(approved, pending, rejected)
edited_atdatetime

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_idrestaurant_iditem_namepriceis_deletedstatusedited_at
11Paneer Tikka2600approved2024-09-01 10:00:00
21Paneer Tikka2800approved2024-09-10 10:00:00
31Paneer Tikka3000pending2024-09-12 09:00:00
41Paneer Tikka2500rejected2024-09-11 09:00:00
51Garlic Naan600approved2024-09-01 10:00:00
61Garlic Naan601approved2024-09-05 18:00:00
71Mango Lassi1200pending2024-09-02 12:00:00
82Masala Dosa1100approved2024-09-03 08:00:00
92Masala Dosa1200approved2024-09-03 08:00:00
102Masala Dosa1300pending2024-09-03 08:00:00
112Masala Dosa1400pending2024-09-01 08:00:00
122Filter Coffee401approved2024-08-20 07:00:00
132Filter Coffee450approved2024-08-25 07:00:00

Output

restaurant_iditem_namelive_pricepending_changes
1Paneer Tikka2801
2Filter Coffee450
2Masala Dosa1201

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_name

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