Zomato: Most-Loved Dish of Each Restaurant and Its Complaints
When diners review a restaurant on Zomato they can tag the dishes the review is about.
- Difficulty: Hard
- Topics: Aggregation, Window Functions, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #1043
Problem statement
When diners review a restaurant on Zomato they can tag the dishes the review is about. The restaurant page highlights each restaurant's most-loved dish — and its owner wants to see the complaints about that same dish.
A dish's loved tags at a restaurant are the reviews rated 4 or 5 that tag it; its complaint tags are the reviews rated 1 or 2 that tag it (3-star reviews count for neither). For each restaurant, return the dish(es) with the most loved tags — every dish tied at the top — with restaurant_name, dish_name, loved_tags and complaint_tags. A restaurant with no loved tag at all does not appear. Order by restaurant_name, then dish_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: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
Primary key: restaurant_id.
One row per restaurant; names are unique.
Table: Review
| Column | Type |
|---|---|
| review_id | int |
| restaurant_id | int |
| rating | int |
| posted_on | date |
Primary key: review_id.
One row per review; rating is 1 to 5 stars.
Table: ReviewDishTag
| Column | Type |
|---|---|
| review_id | int |
| dish_name | varchar |
Primary key: review_id, dish_name.
One row per dish a review tags; a review tags a dish at most once. Dish names are always spelled the same way.
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name |
|---|---|
| 1 | Biryani House |
| 2 | Dosa Darbar |
| 3 | Kebab Korner |
Review
| review_id | restaurant_id | rating | posted_on |
|---|---|---|---|
| 1 | 1 | 5 | 2024-08-01 |
| 2 | 1 | 4 | 2024-08-03 |
| 3 | 1 | 2 | 2024-08-04 |
| 4 | 1 | 5 | 2024-08-09 |
| 5 | 2 | 5 | 2024-08-02 |
| 6 | 2 | 4 | 2024-08-05 |
| 7 | 2 | 1 | 2024-08-07 |
| 8 | 2 | 3 | 2024-08-08 |
| 9 | 3 | 2 | 2024-08-06 |
ReviewDishTag
| review_id | dish_name |
|---|---|
| 1 | Veg Biryani |
| 1 | Gulab Jamun |
| 2 | Gulab Jamun |
| 3 | Gulab Jamun |
| 3 | Veg Biryani |
| 4 | Veg Biryani |
| 5 | Masala Dosa |
| 6 | Filter Coffee |
| 7 | Masala Dosa |
| 8 | Filter Coffee |
| 9 | Butter Chicken |
Output
| restaurant_name | dish_name | loved_tags | complaint_tags |
|---|---|---|---|
| Biryani House | Gulab Jamun | 2 | 1 |
| Biryani House | Veg Biryani | 2 | 1 |
| Dosa Darbar | Filter Coffee | 1 | 0 |
| Dosa Darbar | Masala Dosa | 1 | 1 |
How to solve Zomato: Most-Loved Dish of Each Restaurant and Its Complaints
This is a mode with ties: the most frequent value per group, where every value sharing the top frequency must come back. Because the tags are stored one per row in ReviewDishTag, the frequencies are a plain GROUP BY after joining each tag to its review (the review supplies the rating and the restaurant).
Both counts come from the same groups. SUM(CASE WHEN rating >= 4 …) is the dish's loved tags and SUM(CASE WHEN rating <= 2 …) its complaint tags; 3-star reviews add to neither. Computing them together means the complaint count is ready for whichever dish turns out to be the mode, with no second pass.
To find the mode, rank the dishes inside each restaurant by loved tags with RANK(). Tied dishes share rank 1, so all of them are returned — ROW_NUMBER would pick one arbitrarily and depend on storage order. The loved_tags > 0 filter comes first: a restaurant whose tagged reviews are all complaints would otherwise have its dishes tied at 0 and be reported with a "most-loved" dish that nobody loved.
The alternative counts loved tags only, keeps the dishes whose count equals the restaurant's MAX, and looks up the complaints with a correlated count — the classic pre-window way to compute a mode with ties. Both read the tags once for the counts; the window version also ranks in a single sort per restaurant.
Reference solution (MySQL)
WITH dish_counts AS (
SELECT v.restaurant_id, t.dish_name,
SUM(CASE WHEN v.rating >= 4 THEN 1 ELSE 0 END) AS loved_tags,
SUM(CASE WHEN v.rating <= 2 THEN 1 ELSE 0 END) AS complaint_tags
FROM ReviewDishTag t
JOIN Review v ON v.review_id = t.review_id
GROUP BY v.restaurant_id, t.dish_name
),
ranked AS (
SELECT restaurant_id, dish_name, loved_tags, complaint_tags,
RANK() OVER (PARTITION BY restaurant_id ORDER BY loved_tags DESC) AS pos
FROM dish_counts
WHERE loved_tags > 0
)
SELECT r.restaurant_name, k.dish_name, k.loved_tags, k.complaint_tags
FROM ranked k
JOIN Restaurant r ON r.restaurant_id = k.restaurant_id
WHERE k.pos = 1
ORDER BY r.restaurant_name, k.dish_nameAnother way
WITH loved AS (
SELECT v.restaurant_id, t.dish_name, COUNT(*) AS n
FROM ReviewDishTag t JOIN Review v ON v.review_id = t.review_id
WHERE v.rating IN (4, 5)
GROUP BY v.restaurant_id, t.dish_name
)
SELECT r.restaurant_name, l.dish_name, l.n AS loved_tags,
(SELECT COUNT(*) FROM ReviewDishTag t2 JOIN Review v2 ON v2.review_id = t2.review_id
WHERE v2.restaurant_id = l.restaurant_id AND t2.dish_name = l.dish_name AND v2.rating IN (1, 2)) AS complaint_tags
FROM loved l
JOIN Restaurant r ON r.restaurant_id = l.restaurant_id
WHERE l.n = (SELECT MAX(x.n) FROM loved x WHERE x.restaurant_id = l.restaurant_id)
ORDER BY r.restaurant_name ASC, l.dish_name ASC← Zomato: Cuisine Gaps in Each Locality Worth Recruiting For · Zomato: Restaurant Partner Anniversaries Falling in November →