Zomato: Best-Rated Dining Spot in Each Locality

Medium
1033.

Zomato: Best-Rated Dining Spot in Each Locality

Zomato's dining-out collections feature the best-rated place in every neighbourhood. Ratings come from diners who reviewed a restaurant after eating there.

Only restaurants with at least 3 dining reviews qualify. In each locality, return the qualifying restaurant(s) with the highest average rating — if several share the top average, return all of them. Return locality, restaurant_name, avg_rating (rounded to 2 decimals) and reviews, in any order. A locality with no qualifying restaurant does not appear.

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
localityvarchar

Primary key: restaurant_id.

One row per restaurant.

Table: DiningReview

ColumnType
review_idint
restaurant_idint
ratingint
visited_ondate

Primary key: review_id.

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

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Restaurant

restaurant_idrestaurant_namelocality
1Spice RouteKoramangala
2Wok ExpressKoramangala
3Thali TheatreKoramangala
4Pasta StreetIndiranagar
5Paratha LaneIndiranagar
6Momo PointHSR Layout

DiningReview

review_idrestaurant_idratingvisited_on
1152024-03-01
2142024-03-04
3142024-03-09
4252024-03-02
5252024-03-05
6342024-03-03
7352024-03-06
8342024-03-08
9432024-03-01
10442024-03-02
7 more rows

Output

localityrestaurant_nameavg_ratingreviews
IndiranagarPasta Street44
IndiranagarParatha Lane43
KoramangalaSpice Route4.333
KoramangalaThali Theatre4.333
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: Best-Rated Dining Spot in Each Locality

Zomato's dining-out collections feature the best-rated place in every neighbourhood. Ratings come from diners who reviewed a restaurant after eating there.

  • Difficulty: Medium
  • Topics: Window Functions, Aggregation, Joins
  • Dialect: MySQL
  • Problem: #1033

Problem statement

Zomato's dining-out collections feature the best-rated place in every neighbourhood. Ratings come from diners who reviewed a restaurant after eating there.

Only restaurants with at least 3 dining reviews qualify. In each locality, return the qualifying restaurant(s) with the highest average rating — if several share the top average, return all of them. Return locality, restaurant_name, avg_rating (rounded to 2 decimals) and reviews, in any order. A locality with no qualifying restaurant does not appear.

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
localityvarchar

Primary key: restaurant_id.

One row per restaurant.

Table: DiningReview

ColumnType
review_idint
restaurant_idint
ratingint
visited_ondate

Primary key: review_id.

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

Examples

Example 1

Restaurant

restaurant_idrestaurant_namelocality
1Spice RouteKoramangala
2Wok ExpressKoramangala
3Thali TheatreKoramangala
4Pasta StreetIndiranagar
5Paratha LaneIndiranagar
6Momo PointHSR Layout

DiningReview

review_idrestaurant_idratingvisited_on
1152024-03-01
2142024-03-04
3142024-03-09
4252024-03-02
5252024-03-05
6342024-03-03
7352024-03-06
8342024-03-08
9432024-03-01
10442024-03-02
11452024-03-07
12442024-03-10
13542024-03-04
14542024-03-05
15542024-03-11
16622024-03-02
17632024-03-12

Output

localityrestaurant_nameavg_ratingreviews
IndiranagarPasta Street44
IndiranagarParatha Lane43
KoramangalaSpice Route4.333
KoramangalaThali Theatre4.333

How to solve Zomato: Best-Rated Dining Spot in Each Locality

Work in two steps. First aggregate the reviews per restaurant: AVG(rating) and COUNT(*), with HAVING COUNT(*) >= 3 dropping thinly reviewed places before any ranking — otherwise a restaurant with two five-star reviews would beat everyone.

Then rank within each locality. RANK() OVER (PARTITION BY locality ORDER BY avg_raw DESC) numbers the restaurants of one locality from the best down, and equal averages receive the same rank, so every restaurant tied for the top gets position 1 and is kept. ROW_NUMBER would silently choose one of them — the classic mistake on a "return all ties" question.

Rank on the unrounded average and round only in the final SELECT; ranking on rounded values could tie two averages that differ in the third decimal. A locality whose restaurants all have fewer than 3 reviews leaves no row in scored and so no answer.

The alternative avoids window functions: a restaurant is the best when no restaurant of the same locality has a higher average, and the comparison t.total * s.reviews > s.total * t.reviews compares the two averages exactly by cross-multiplying, with no division. The window version reads the aggregate once; the NOT EXISTS version compares pairs within a locality.

Reference solution (MySQL)

WITH scored AS (
  SELECT r.locality, r.restaurant_name, AVG(d.rating) AS avg_raw, COUNT(*) AS reviews
  FROM Restaurant r
  JOIN DiningReview d ON d.restaurant_id = r.restaurant_id
  GROUP BY r.restaurant_id, r.locality, r.restaurant_name
  HAVING COUNT(*) >= 3
),
ranked AS (
  SELECT locality, restaurant_name, avg_raw, reviews,
         RANK() OVER (PARTITION BY locality ORDER BY avg_raw DESC) AS pos
  FROM scored
)
SELECT locality, restaurant_name, ROUND(avg_raw, 2) AS avg_rating, reviews
FROM ranked
WHERE pos = 1

Another way

WITH scored AS (
  SELECT r.locality, r.restaurant_name, SUM(d.rating) AS total, COUNT(*) AS reviews
  FROM Restaurant r JOIN DiningReview d ON d.restaurant_id = r.restaurant_id
  GROUP BY r.restaurant_id, r.locality, r.restaurant_name
  HAVING COUNT(*) >= 3
)
SELECT s.locality, s.restaurant_name, ROUND(s.total / s.reviews, 2) AS avg_rating, s.reviews
FROM scored s
WHERE NOT EXISTS (
  SELECT 1 FROM scored t
  WHERE t.locality = s.locality AND t.total * s.reviews > s.total * t.reviews
)

← Zomato: Cost per Order of Each Promoted Listing · Zomato: Reviews Far From the Restaurant's Consensus →