Restaurants Averaging Under 3.5 Stars

Easy
154.

Restaurants Averaging Under 3.5 Stars

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

ColumnType
restaurant_idint
namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant.

Table: Review

ColumnType
review_idint
restaurant_idint
customer_idint
starsint
reviewed_ondate

Primary key: review_id.

One row per review, stars from 1 to 5. restaurant_id is always in Restaurant.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idnamecity
1Burger BarnPune
2Wok ExpressPune
3Curry LeafKochi
4Pizza PlanetPune

Review

review_idrestaurant_idcustomer_idstarsreviewed_on
113122024-05-01
213242024-05-02
313332024-05-04
423132024-05-03
523442024-05-06
623532024-05-07
723642024-05-09
833212024-05-05
933722024-05-08
1043822024-05-02
2 more rows

Output

restaurant_idnamereviewsavg_stars
1Burger Barn33
4Pizza Planet33
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.

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

ColumnType
restaurant_idint
namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant.

Table: Review

ColumnType
review_idint
restaurant_idint
customer_idint
starsint
reviewed_ondate

Primary key: review_id.

One row per review, stars from 1 to 5. restaurant_id is always in Restaurant.

Examples

Example 1

Restaurant

restaurant_idnamecity
1Burger BarnPune
2Wok ExpressPune
3Curry LeafKochi
4Pizza PlanetPune

Review

review_idrestaurant_idcustomer_idstarsreviewed_on
113122024-05-01
213242024-05-02
313332024-05-04
423132024-05-03
523442024-05-06
623532024-05-07
723642024-05-09
833212024-05-05
933722024-05-08
1043822024-05-02
1143932024-05-03
1243142024-05-06

Output

restaurant_idnamereviewsavg_stars
1Burger Barn33
4Pizza Planet33

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_id

Another 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_id

Another 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 →