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
| Column | Type |
|---|---|
| place_id | int |
| place_name | varchar |
| city | varchar |
Primary key: place_id.
One row per place listed on Maps.
Table: PlaceReview
| Column | Type |
|---|---|
| review_id | int |
| place_id | int |
| reviewer | varchar |
| rating | int |
| is_spam | bool |
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_id | place_name | city |
|---|---|---|
| 1 | Vidyarthi Bhavan | Bengaluru |
| 2 | Leopold Cafe | Mumbai |
| 3 | Karim's | Delhi |
| 4 | Peter Cat | Kolkata |
PlaceReview
| review_id | place_id | reviewer | rating | is_spam |
|---|---|---|---|---|
| 1 | 1 | Aarav | 5 | 0 |
| 2 | 1 | Diya | 4 | 0 |
| 3 | 1 | Kabir | 5 | 0 |
| 4 | 1 | Neha | 4 | 0 |
| 5 | 2 | Rohan | 5 | 0 |
| 6 | 2 | Zara | 5 | 0 |
| 7 | 2 | Ishaan | 5 | 1 |
| 8 | 3 | Meera | 5 | 0 |
| 9 | 3 | Priya | 5 | 0 |
| 10 | 3 | Tanvi | 1 | 1 |
| 11 | 3 | Riya | 4 | 0 |
| 12 | 4 | Dev | 3 | 0 |
Output
| place_id | place_name | review_count |
|---|---|---|
| 1 | Vidyarthi Bhavan | 4 |
| 3 | Karim's | 3 |
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.5Another 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 * 9Another 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 →