Restaurants Averaging Under 3.5 Stars — SQL Problem
The partner-quality team puts a restaurant on a watch list when it has at least 3 reviews and its average rating is strictly below 3.5 stars (computed on…
- Difficulty: Easy
- Topics: Aggregation, Joins
- Dialect: MySQL
- Problem: #154
Problem statement
The partner-quality team puts a restaurant on a watch list when it has at least 3 reviews and its average rating is strictly below 3.5 stars (computed on the exact average, before any rounding).
Return restaurant_id, name, reviews (the number of reviews) and avg_stars (the average rounded to 2 decimal places) for every restaurant on the watch list, ordered by avg_stars ascending, then restaurant_id ascending.
Tables
Table: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| name | varchar |
| city | varchar |
Primary key: restaurant_id.
One row per restaurant.
Table: Review
| Column | Type |
|---|---|
| review_id | int |
| restaurant_id | int |
| customer_id | int |
| stars | int |
| reviewed_on | date |
Primary key: review_id.
One row per review, stars from 1 to 5. restaurant_id is always in Restaurant.
Examples
Example 1
Restaurant
| restaurant_id | name | city |
|---|---|---|
| 1 | Burger Barn | Pune |
| 2 | Wok Express | Pune |
| 3 | Curry Leaf | Kochi |
| 4 | Pizza Planet | Pune |
Review
| review_id | restaurant_id | customer_id | stars | reviewed_on |
|---|---|---|---|---|
| 1 | 1 | 31 | 2 | 2024-05-01 |
| 2 | 1 | 32 | 4 | 2024-05-02 |
| 3 | 1 | 33 | 3 | 2024-05-04 |
| 4 | 2 | 31 | 3 | 2024-05-03 |
| 5 | 2 | 34 | 4 | 2024-05-06 |
| 6 | 2 | 35 | 3 | 2024-05-07 |
| 7 | 2 | 36 | 4 | 2024-05-09 |
| 8 | 3 | 32 | 1 | 2024-05-05 |
| 9 | 3 | 37 | 2 | 2024-05-08 |
| 10 | 4 | 38 | 2 | 2024-05-02 |
| 11 | 4 | 39 | 3 | 2024-05-03 |
| 12 | 4 | 31 | 4 | 2024-05-06 |
Output
| restaurant_id | name | reviews | avg_stars |
|---|---|---|---|
| 1 | Burger Barn | 3 | 3 |
| 4 | Pizza Planet | 3 | 3 |
How to solve Restaurants Averaging Under 3.5 Stars
One GROUP BY per restaurant gives the count and the average; HAVING keeps the groups that pass both tests. WHERE cannot do it, because it runs on single review rows before any group exists.
The threshold is on the exact average. Comparing the rounded value would be wrong at the edge — an average of 3.4986 rounds to 3.50 yet is below 3.5 — so the HAVING uses AVG(stars) and only the output column is rounded. An exactly 3.5 average (3, 4, 3, 4) is not strictly below and stays off the list. Integer arithmetic avoids decimals entirely: SUM(stars) * 2 < COUNT(*) * 7.
Joining Restaurant first and grouping by its id and name, or aggregating Review in a derived table and joining after, are equivalent; restaurants with fewer than three reviews (including none) fail the count either way. The cost is one pass over the reviews.
Reference solution (MySQL)
SELECT r.restaurant_id, r.name, COUNT(*) AS reviews, ROUND(AVG(v.stars), 2) AS avg_stars
FROM Restaurant r
JOIN Review v ON v.restaurant_id = r.restaurant_id
GROUP BY r.restaurant_id, r.name
HAVING COUNT(*) >= 3 AND AVG(v.stars) < 3.5
ORDER BY avg_stars, r.restaurant_idAnother way
SELECT r.restaurant_id, r.name, a.reviews, a.avg_stars
FROM (SELECT restaurant_id, COUNT(*) AS reviews, ROUND(AVG(stars), 2) AS avg_stars, SUM(stars) AS total
FROM Review GROUP BY restaurant_id) a
JOIN Restaurant r ON r.restaurant_id = a.restaurant_id
WHERE a.reviews >= 3 AND a.total * 2 < a.reviews * 7
ORDER BY a.avg_stars, r.restaurant_idAnother way
SELECT r.restaurant_id, r.name, COUNT(v.review_id) AS reviews, ROUND(SUM(v.stars) / COUNT(v.review_id), 2) AS avg_stars FROM Restaurant r LEFT JOIN Review v ON v.restaurant_id = r.restaurant_id GROUP BY r.restaurant_id, r.name HAVING COUNT(v.review_id) > 2 AND SUM(v.stars) < 3.5 * COUNT(v.review_id) ORDER BY 4, 1← Weekend Food Orders in March 2024 · Refund Due on Cancelled Food Orders →