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
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
| locality | varchar |
Primary key: restaurant_id.
One row per restaurant.
Table: DiningReview
| Column | Type |
|---|---|
| review_id | int |
| restaurant_id | int |
| rating | int |
| visited_on | date |
Primary key: review_id.
One row per dining review; rating is 1 to 5 stars.
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name | locality |
|---|---|---|
| 1 | Spice Route | Koramangala |
| 2 | Wok Express | Koramangala |
| 3 | Thali Theatre | Koramangala |
| 4 | Pasta Street | Indiranagar |
| 5 | Paratha Lane | Indiranagar |
| 6 | Momo Point | HSR Layout |
DiningReview
| review_id | restaurant_id | rating | visited_on |
|---|---|---|---|
| 1 | 1 | 5 | 2024-03-01 |
| 2 | 1 | 4 | 2024-03-04 |
| 3 | 1 | 4 | 2024-03-09 |
| 4 | 2 | 5 | 2024-03-02 |
| 5 | 2 | 5 | 2024-03-05 |
| 6 | 3 | 4 | 2024-03-03 |
| 7 | 3 | 5 | 2024-03-06 |
| 8 | 3 | 4 | 2024-03-08 |
| 9 | 4 | 3 | 2024-03-01 |
| 10 | 4 | 4 | 2024-03-02 |
| 11 | 4 | 5 | 2024-03-07 |
| 12 | 4 | 4 | 2024-03-10 |
| 13 | 5 | 4 | 2024-03-04 |
| 14 | 5 | 4 | 2024-03-05 |
| 15 | 5 | 4 | 2024-03-11 |
| 16 | 6 | 2 | 2024-03-02 |
| 17 | 6 | 3 | 2024-03-12 |
Output
| locality | restaurant_name | avg_rating | reviews |
|---|---|---|---|
| Indiranagar | Pasta Street | 4 | 4 |
| Indiranagar | Paratha Lane | 4 | 3 |
| Koramangala | Spice Route | 4.33 | 3 |
| Koramangala | Thali Theatre | 4.33 | 3 |
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 = 1Another 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 →