Zomato: Most-Loved Dish of Each Restaurant and Its Complaints

Hard
1043.

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

ColumnType
restaurant_idint
restaurant_namevarchar

Primary key: restaurant_id.

One row per restaurant; names are unique.

Table: Review

ColumnType
review_idint
restaurant_idint
ratingint
posted_ondate

Primary key: review_id.

One row per review; rating is 1 to 5 stars.

Table: ReviewDishTag

ColumnType
review_idint
dish_namevarchar

Primary key (together): 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.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_name
1Biryani House
2Dosa Darbar
3Kebab Korner

Review

review_idrestaurant_idratingposted_on
1152024-08-01
2142024-08-03
3122024-08-04
4152024-08-09
5252024-08-02
6242024-08-05
7212024-08-07
8232024-08-08
9322024-08-06

ReviewDishTag

review_iddish_name
1Veg Biryani
1Gulab Jamun
2Gulab Jamun
3Gulab Jamun
3Veg Biryani
4Veg Biryani
5Masala Dosa
6Filter Coffee
7Masala Dosa
8Filter Coffee
1 more row

Output

restaurant_namedish_nameloved_tagscomplaint_tags
Biryani HouseGulab Jamun21
Biryani HouseVeg Biryani21
Dosa DarbarFilter Coffee10
Dosa DarbarMasala Dosa11
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: 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

ColumnType
restaurant_idint
restaurant_namevarchar

Primary key: restaurant_id.

One row per restaurant; names are unique.

Table: Review

ColumnType
review_idint
restaurant_idint
ratingint
posted_ondate

Primary key: review_id.

One row per review; rating is 1 to 5 stars.

Table: ReviewDishTag

ColumnType
review_idint
dish_namevarchar

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_idrestaurant_name
1Biryani House
2Dosa Darbar
3Kebab Korner

Review

review_idrestaurant_idratingposted_on
1152024-08-01
2142024-08-03
3122024-08-04
4152024-08-09
5252024-08-02
6242024-08-05
7212024-08-07
8232024-08-08
9322024-08-06

ReviewDishTag

review_iddish_name
1Veg Biryani
1Gulab Jamun
2Gulab Jamun
3Gulab Jamun
3Veg Biryani
4Veg Biryani
5Masala Dosa
6Filter Coffee
7Masala Dosa
8Filter Coffee
9Butter Chicken

Output

restaurant_namedish_nameloved_tagscomplaint_tags
Biryani HouseGulab Jamun21
Biryani HouseVeg Biryani21
Dosa DarbarFilter Coffee10
Dosa DarbarMasala Dosa11

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_name

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