Sweaty Imposter

Products With a High Rating and Enough Reviews

SQLAggregationJuniorMidUpdated
Return each product_id with an average rating of at least 4 across at least 10 reviews, along with the average and the review count.

Schema

CREATE TABLE reviews (
  id          BIGINT PRIMARY KEY,
  product_id  BIGINT NOT NULL,
  rating      INT NOT NULL CHECK (rating BETWEEN 1 AND 5)
);

Drill This: Your Query First

The Correct Query

Option (b)

SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
HAVING AVG(rating) >= 4 AND COUNT(*) >= 10;
Both conditions are about the whole group, so both belong in HAVING, joined with AND. (a) filters rows before grouping: it averages and counts only the 4 and 5 star reviews, so a product with ten 5s and ten 1s passes with a reported average of 5.0. (c) accepts a product that meets either condition, including one with hundreds of 2 star reviews. (d) drops the count, so a single 5 star review is enough.

Going Deeper

Put a condition in WHERE when it applies to individual rows (only reviews from the last year) and in HAVING when it applies to an aggregate (AVG, COUNT, SUM). The two combine: WHERE created_at >= now() - interval '1 year' with HAVING COUNT(*) >= 10 means at least 10 reviews in the last year. In Postgres, AVG of an INT column returns NUMERIC, but SUM(rating) / COUNT(*) divides two integers and truncates, so a 4.6 average comes out as 4.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews
WHERE rating >= 4
GROUP BY product_id
HAVING COUNT(*) >= 10;

Option (c)

SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
HAVING AVG(rating) >= 4 OR COUNT(*) >= 10;

Option (d)

SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
HAVING AVG(rating) >= 4;