Zomato: Ratings Before and After a Hygiene Audit

Hard
1047.

Zomato: Ratings Before and After a Hygiene Audit

After a hygiene audit a restaurant's new grade is shown on its page. The trust-and-safety team wants to see whether diners rate a restaurant differently after an audit.

For each audit, take the restaurant's reviews posted in the 30 days before the audit (audit_date − 30 to audit_date − 1) and the 30 days after it (audit_date + 1 to audit_date + 30); reviews on the audit day itself are ignored. Report only audits with at least 2 reviews in each window. Return audit_id, restaurant_name, grade, avg_before and avg_after (each rounded to 2 decimals) and rating_change = avg_after − avg_before, the difference of the two rounded averages as the page shows them. Order by rating_change descending, then audit_id ascending.

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.

Table: HygieneAudit

ColumnTypeValues
audit_idint
restaurant_idint
audit_datedate
gradeenumExcellent, Very Good, Good, Needs Improvement

Primary key: audit_id.

One row per audit; a restaurant may be audited more than once.

Table: RestaurantReview

ColumnType
review_idint
restaurant_idint
ratingint
posted_ondate

Primary key: review_id.

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

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_name
1Chaat Junction
2Coastal Curry
3Idli Express

HygieneAudit

audit_idrestaurant_idaudit_dategrade
112024-05-15Very Good
222024-05-20Excellent
332024-06-01Good

RestaurantReview

review_idrestaurant_idratingposted_on
1132024-04-20
2122024-05-10
3152024-05-15
4142024-05-20
5152024-06-14
6112024-04-14
7242024-05-01
8242024-05-19
9232024-05-21
10242024-06-10
4 more rows

Output

audit_idrestaurant_namegradeavg_beforeavg_afterrating_change
1Chaat JunctionVery Good2.54.52
2Coastal CurryExcellent43.67-0.33
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: Ratings Before and After a Hygiene Audit

After a hygiene audit a restaurant's new grade is shown on its page.

  • Difficulty: Hard
  • Topics: Joins, Aggregation, Conditional Logic, Dates
  • Dialect: MySQL
  • Problem: #1047

Problem statement

After a hygiene audit a restaurant's new grade is shown on its page. The trust-and-safety team wants to see whether diners rate a restaurant differently after an audit.

For each audit, take the restaurant's reviews posted in the 30 days before the audit (audit_date − 30 to audit_date − 1) and the 30 days after it (audit_date + 1 to audit_date + 30); reviews on the audit day itself are ignored. Report only audits with at least 2 reviews in each window. Return audit_id, restaurant_name, grade, avg_before and avg_after (each rounded to 2 decimals) and rating_change = avg_after − avg_before, the difference of the two rounded averages as the page shows them. Order by rating_change descending, then audit_id ascending.

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.

Table: HygieneAudit

ColumnType
audit_idint
restaurant_idint
audit_datedate
gradeenum(Excellent, Very Good, Good, Needs Improvement)

Primary key: audit_id.

One row per audit; a restaurant may be audited more than once.

Table: RestaurantReview

ColumnType
review_idint
restaurant_idint
ratingint
posted_ondate

Primary key: review_id.

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

Examples

Example 1

Restaurant

restaurant_idrestaurant_name
1Chaat Junction
2Coastal Curry
3Idli Express

HygieneAudit

audit_idrestaurant_idaudit_dategrade
112024-05-15Very Good
222024-05-20Excellent
332024-06-01Good

RestaurantReview

review_idrestaurant_idratingposted_on
1132024-04-20
2122024-05-10
3152024-05-15
4142024-05-20
5152024-06-14
6112024-04-14
7242024-05-01
8242024-05-19
9232024-05-21
10242024-06-10
11242024-06-12
12342024-05-25
13352024-06-05
14352024-06-20

Output

audit_idrestaurant_namegradeavg_beforeavg_afterrating_change
1Chaat JunctionVery Good2.54.52
2Coastal CurryExcellent43.67-0.33

How to solve Zomato: Ratings Before and After a Hygiene Audit

Each audit defines its own pair of windows, so the reviews are joined to the audit, not just to the restaurant: same restaurant, posted_on within 30 days either side (DATE_SUB/DATE_ADD bound the range inclusively) and not on the audit day. A restaurant audited twice has its reviews joined to each audit separately, and a review may fall into windows of both.

Grouping by audit puts all of its nearby reviews in one group, and conditional aggregation splits them: AVG(CASE WHEN posted_on < audit_date THEN rating END) averages only the before-window, because the CASE yields NULL for the after-window rows and AVG ignores NULLs; the same with > gives the after-average. The minimum-size rule is two conditional counts in HAVING — an audit with one review on a side would show a change based on a single opinion.

The change is the difference of the two rounded averages, so the row's three numbers always agree with each other (3.20 − 3.38 = −0.18). Rounding the unrounded difference instead can disagree with them by a cent, and lands on a .5 when an average ends in 5 at the third decimal (3.375 with eight reviews) — exactly where engines disagree about which way to round. Subtracting two 2-decimal figures is exact, and the outer ROUND(…, 2) only removes binary noise so equal changes compare equal in the ordering. The order uses that change with audit_id as tie-break.

The alternative computes the before and after windows in two separate aggregates and joins them — an inner join to both also enforces that each window has reviews. It reads the reviews twice instead of once, but each part is simpler to check.

Reference solution (MySQL)

SELECT a.audit_id, r.restaurant_name, a.grade,
       ROUND(AVG(CASE WHEN v.posted_on < a.audit_date THEN v.rating END), 2) AS avg_before,
       ROUND(AVG(CASE WHEN v.posted_on > a.audit_date THEN v.rating END), 2) AS avg_after,
       ROUND(ROUND(AVG(CASE WHEN v.posted_on > a.audit_date THEN v.rating END), 2)
           - ROUND(AVG(CASE WHEN v.posted_on < a.audit_date THEN v.rating END), 2), 2) AS rating_change
FROM HygieneAudit a
JOIN Restaurant r ON r.restaurant_id = a.restaurant_id
JOIN RestaurantReview v
  ON v.restaurant_id = a.restaurant_id
 AND v.posted_on BETWEEN DATE_SUB(a.audit_date, INTERVAL 30 DAY) AND DATE_ADD(a.audit_date, INTERVAL 30 DAY)
 AND v.posted_on <> a.audit_date
GROUP BY a.audit_id, r.restaurant_name, a.grade
HAVING COUNT(CASE WHEN v.posted_on < a.audit_date THEN 1 END) >= 2
   AND COUNT(CASE WHEN v.posted_on > a.audit_date THEN 1 END) >= 2
ORDER BY rating_change DESC, a.audit_id

Another way

WITH before_w AS (
  SELECT a.audit_id, AVG(v.rating) AS avg_b, COUNT(*) AS n_b
  FROM HygieneAudit a JOIN RestaurantReview v ON v.restaurant_id = a.restaurant_id
  WHERE v.posted_on >= DATE_SUB(a.audit_date, INTERVAL 30 DAY) AND v.posted_on < a.audit_date
  GROUP BY a.audit_id
),
after_w AS (
  SELECT a.audit_id, AVG(v.rating) AS avg_a, COUNT(*) AS n_a
  FROM HygieneAudit a JOIN RestaurantReview v ON v.restaurant_id = a.restaurant_id
  WHERE v.posted_on > a.audit_date AND v.posted_on <= DATE_ADD(a.audit_date, INTERVAL 30 DAY)
  GROUP BY a.audit_id
)
SELECT a.audit_id, r.restaurant_name, a.grade,
       ROUND(b.avg_b, 2) AS avg_before, ROUND(f.avg_a, 2) AS avg_after,
       ROUND(ROUND(f.avg_a, 2) - ROUND(b.avg_b, 2), 2) AS rating_change
FROM HygieneAudit a
JOIN Restaurant r ON r.restaurant_id = a.restaurant_id
JOIN before_w b ON b.audit_id = a.audit_id
JOIN after_w f ON f.audit_id = a.audit_id
WHERE b.n_b >= 2 AND f.n_a >= 2
ORDER BY rating_change DESC, a.audit_id ASC

← Zomato: Orders Placed While the Restaurant Was Closed