Google: Well-Rated Places With Enough Reviews

Easy
676.

Google: Well-Rated Places With Enough Reviews

Google Maps wants to badge restaurants that diners consistently love, but only where there is enough evidence and only from genuine reviews.

Ignoring every review with is_spam = 1, return place_id, place_name and review_count (its genuine reviews) for each place with at least 3 genuine reviews whose average genuine rating is at least 4.5. Return the rows in any order.

A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.

Tables

Table: Place

ColumnType
place_idint
place_namevarchar
cityvarchar

Primary key: place_id.

One row per place listed on Maps.

Table: PlaceReview

ColumnType
review_idint
place_idint
reviewervarchar
ratingint
is_spambool

Primary key: review_id.

rating is 1–5 stars. is_spam is 1 for a review the spam classifier removed.

Example

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

Place

place_idplace_namecity
1Vidyarthi BhavanBengaluru
2Leopold CafeMumbai
3Karim'sDelhi
4Peter CatKolkata

PlaceReview

review_idplace_idreviewerratingis_spam
11Aarav50
21Diya40
31Kabir50
41Neha40
52Rohan50
62Zara50
72Ishaan51
83Meera50
93Priya50
103Tanvi11
2 more rows

Output

place_idplace_namereview_count
1Vidyarthi Bhavan4
3Karim's3
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.

Google: Well-Rated Places With Enough Reviews — SQL Problem

Google Maps wants to badge restaurants that diners consistently love, but only where there is enough evidence and only from genuine reviews.

  • Difficulty: Easy
  • Topics: Aggregation, Joins
  • Dialect: MySQL
  • Problem: #676

Problem statement

Google Maps wants to badge restaurants that diners consistently love, but only where there is enough evidence and only from genuine reviews.

Ignoring every review with is_spam = 1, return place_id, place_name and review_count (its genuine reviews) for each place with at least 3 genuine reviews whose average genuine rating is at least 4.5. Return the rows in any order.

A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.

Tables

Table: Place

ColumnType
place_idint
place_namevarchar
cityvarchar

Primary key: place_id.

One row per place listed on Maps.

Table: PlaceReview

ColumnType
review_idint
place_idint
reviewervarchar
ratingint
is_spambool

Primary key: review_id.

rating is 1–5 stars. is_spam is 1 for a review the spam classifier removed.

Examples

Example 1

Place

place_idplace_namecity
1Vidyarthi BhavanBengaluru
2Leopold CafeMumbai
3Karim'sDelhi
4Peter CatKolkata

PlaceReview

review_idplace_idreviewerratingis_spam
11Aarav50
21Diya40
31Kabir50
41Neha40
52Rohan50
62Zara50
72Ishaan51
83Meera50
93Priya50
103Tanvi11
113Riya40
124Dev30

Output

place_idplace_namereview_count
1Vidyarthi Bhavan4
3Karim's3

How to solve Google: Well-Rated Places With Enough Reviews

Spam reviews must not count toward either threshold, so they are removed in WHERE before grouping — filtering rows before aggregation is what WHERE is for. Grouping the remaining reviews by place gives each place's genuine review count and average rating, and both conditions go into HAVING: COUNT(*) >= 3 and AVG(r.rating) >= 4.5.

Both comparisons are inclusive. A place with ratings 5, 4, 5, 4 averages exactly 4.5 and earns the badge. A place whose only spam-free reviews number two is excluded however perfect they are; and if every review of a place is spam it forms no group at all.

The first alternative avoids a fractional average by comparing totals — 2 × stars ≥ 9 × reviews is the same test as stars / reviews ≥ 4.5 — which is a handy trick when exactness matters. The second keeps spam rows in the join and filters inside the aggregates with IF. All of them are one grouped scan of the reviews.

Reference solution (MySQL)

SELECT p.place_id, p.place_name, COUNT(*) AS review_count
FROM Place p
JOIN PlaceReview r ON r.place_id = p.place_id
WHERE r.is_spam = 0
GROUP BY p.place_id, p.place_name
HAVING COUNT(*) >= 3 AND AVG(r.rating) >= 4.5

Another way

SELECT p.place_id, p.place_name, t.review_count FROM Place p JOIN (SELECT place_id, COUNT(*) AS review_count, SUM(rating) AS stars FROM PlaceReview WHERE is_spam = 0 GROUP BY place_id) t ON t.place_id = p.place_id WHERE t.review_count >= 3 AND t.stars * 2 >= t.review_count * 9

Another way

SELECT p.place_id, p.place_name, SUM(IF(r.is_spam = 0, 1, 0)) AS review_count FROM Place p JOIN PlaceReview r ON r.place_id = p.place_id GROUP BY p.place_id, p.place_name HAVING SUM(IF(r.is_spam = 0, 1, 0)) >= 3 AND SUM(IF(r.is_spam = 0, r.rating, 0)) >= 4.5 * SUM(IF(r.is_spam = 0, 1, 0))

← Google: Accounts Over Their Storage Quota · Google: March Cloud Bill for Every Project →