Zomato: Reviews Far From the Restaurant's Consensus

Medium
1034.

Zomato: Reviews Far From the Restaurant's Consensus

Zomato's review-moderation team looks at ratings that disagree sharply with everyone else's view of a restaurant — they are often fake or a mistaken tap.

For each review, the consensus is the average rating of all the other reviews of the same restaurant (the review itself is left out). Return the reviews whose rating differs from that consensus by 2 stars or more (in either direction), with review_id, restaurant_id, rating and others_avg (the consensus rounded to 2 decimals). A review that is its restaurant's only review has no consensus and is never returned. Order by review_id.

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: Review

ColumnType
review_idint
restaurant_idint
user_idint
ratingint
posted_ondate

Primary key: review_id.

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

Example

The rows must come back in the order shown.

Review

review_idrestaurant_iduser_idratingposted_on
11020152024-04-01
21020242024-04-02
31020312024-04-02
41020452024-04-05
51120532024-04-03
61220112024-04-04
71220632024-04-06
81220732024-04-07
91320852024-04-01
101320922024-04-08
1 more row

Output

review_idrestaurant_idratingothers_avg
31014.67
61213
91351.5
111313.5
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: Reviews Far From the Restaurant's Consensus

Zomato's review-moderation team looks at ratings that disagree sharply with everyone else's view of a restaurant — they are often fake or a mistaken tap.

  • Difficulty: Medium
  • Topics: Window Functions, Subqueries
  • Dialect: MySQL
  • Problem: #1034

Problem statement

Zomato's review-moderation team looks at ratings that disagree sharply with everyone else's view of a restaurant — they are often fake or a mistaken tap.

For each review, the consensus is the average rating of all the other reviews of the same restaurant (the review itself is left out). Return the reviews whose rating differs from that consensus by 2 stars or more (in either direction), with review_id, restaurant_id, rating and others_avg (the consensus rounded to 2 decimals). A review that is its restaurant's only review has no consensus and is never returned. Order by review_id.

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: Review

ColumnType
review_idint
restaurant_idint
user_idint
ratingint
posted_ondate

Primary key: review_id.

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

Examples

Example 1

Review

review_idrestaurant_iduser_idratingposted_on
11020152024-04-01
21020242024-04-02
31020312024-04-02
41020452024-04-05
51120532024-04-03
61220112024-04-04
71220632024-04-06
81220732024-04-07
91320852024-04-01
101320922024-04-08
111321012024-04-09

Output

review_idrestaurant_idratingothers_avg
31014.67
61213
91351.5
111313.5

How to solve Zomato: Reviews Far From the Restaurant's Consensus

The consensus for a review excludes the review itself, so it is not the restaurant's plain average. It can be derived from two restaurant-level numbers: (total - rating) / (cnt - 1), where total and cnt are the sum and the number of all its ratings. Window aggregates with PARTITION BY restaurant_id and no ORDER BY attach those two numbers to every review row without collapsing the rows, so every review can see its own restaurant's figures.

A restaurant with one review has cnt - 1 = 0; such reviews have no consensus and the cnt > 1 test removes them before anything divides. The flag is ABS(rating - others_avg) >= 2, which catches both a one-star review among fives and a five among ones, and includes a gap of exactly 2.

The comparison uses the exact average; only the displayed column is rounded, so a consensus of 3.33 against a 1-star review is judged on 3.333…, not on the rounded figure.

The alternative computes each consensus with a correlated AVG over the other reviews (o.review_id <> r.review_id); for a lone review that subquery averages nothing and returns NULL, which the IS NOT NULL test drops. It is simpler to read but scans a restaurant's reviews once per review, while the window version needs a single pass.

Reference solution (MySQL)

WITH t AS (
  SELECT review_id, restaurant_id, rating,
         SUM(rating) OVER (PARTITION BY restaurant_id) AS total,
         COUNT(*) OVER (PARTITION BY restaurant_id) AS cnt
  FROM Review
)
SELECT review_id, restaurant_id, rating,
       ROUND((total - rating) / (cnt - 1), 2) AS others_avg
FROM t
WHERE cnt > 1
  AND ABS(rating - (total - rating) / (cnt - 1)) >= 2
ORDER BY review_id

Another way

SELECT review_id, restaurant_id, rating, ROUND(others_avg, 2) AS others_avg FROM (
  SELECT r.review_id, r.restaurant_id, r.rating,
         (SELECT AVG(o.rating) FROM Review o
          WHERE o.restaurant_id = r.restaurant_id AND o.review_id <> r.review_id) AS others_avg
  FROM Review r
) x
WHERE others_avg IS NOT NULL AND (rating >= others_avg + 2 OR rating <= others_avg - 2)
ORDER BY review_id

← Zomato: Best-Rated Dining Spot in Each Locality · Zomato: Blinkit Stock-Outs Lasting a Day or More →