Best-Rated Product in Each Category

Medium
108.

Best-Rated Product in Each Category

The homepage shows a "Top rated" product per category. A product qualifies only with at least 3 reviews; among the qualifying products of a category, the one with the highest average rating wins, and products tied on that average all win.

Return category, product_name, avg_rating (the average rating rounded to 2 decimals) and reviews (the review count) for every winner. A category with no qualifying product does not appear. Order the rows by category, then product_name.

Tables

Table: products

ColumnType
product_idint
product_namevarchar
categoryvarchar

Primary key: product_id.

One row per listed product; names are unique.

Table: product_reviews

ColumnType
review_idint
product_idint
customer_idint
ratingint

Primary key: review_id.

One row per review; rating is 1–5 stars. product_id is always in products.

Example

The rows must come back in the order shown.

products

product_idproduct_namecategory
1Galaxy M34Mobiles
2Moto G54Mobiles
3Nord CE 3Mobiles
4Silk SareeFashion
5Linen ShirtFashion
6Mixer GrinderHome

product_reviews

review_idproduct_idcustomer_idrating
11115
21124
31134
42145
52155
63163
73175
83184
93195
103205
9 more rows

Output

categoryproduct_nameavg_ratingreviews
FashionLinen Shirt43
FashionSilk Saree43
MobilesGalaxy M344.333
MobilesNord CE 34.336
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.

Best-Rated Product in Each Category — SQL Problem

The homepage shows a "Top rated" product per category. A product qualifies only with at least 3 reviews; among the qualifying products of a category, the…

  • Difficulty: Medium
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #108

Problem statement

The homepage shows a "Top rated" product per category. A product qualifies only with at least 3 reviews; among the qualifying products of a category, the one with the highest average rating wins, and products tied on that average all win.

Return category, product_name, avg_rating (the average rating rounded to 2 decimals) and reviews (the review count) for every winner. A category with no qualifying product does not appear. Order the rows by category, then product_name.

Tables

Table: products

ColumnType
product_idint
product_namevarchar
categoryvarchar

Primary key: product_id.

One row per listed product; names are unique.

Table: product_reviews

ColumnType
review_idint
product_idint
customer_idint
ratingint

Primary key: review_id.

One row per review; rating is 1–5 stars. product_id is always in products.

Examples

Example 1

products

product_idproduct_namecategory
1Galaxy M34Mobiles
2Moto G54Mobiles
3Nord CE 3Mobiles
4Silk SareeFashion
5Linen ShirtFashion
6Mixer GrinderHome

product_reviews

review_idproduct_idcustomer_idrating
11115
21124
31134
42145
52155
63163
73175
83184
93195
103205
113214
124225
134234
144243
155254
165264
175274
186282
196295

Output

categoryproduct_nameavg_ratingreviews
FashionLinen Shirt43
FashionSilk Saree43
MobilesGalaxy M344.333
MobilesNord CE 34.336

How to solve Best-Rated Product in Each Category

Two steps. First aggregate the reviews per product — average and count — and drop products below 3 reviews with HAVING. The threshold must be applied before ranking: a product with two five-star reviews (Moto G54 in the example) has the best average in its category but is not eligible, and if it were ranked first it would hide the real winner.

Second, rank within each category: RANK() OVER (PARTITION BY category ORDER BY avg DESC) and keep rank 1. Tied products get the same rank, so all of them are kept, as the statement asks. Rank on the raw average and round only for display; rounding first could make two different averages look tied.

The alternative avoids window functions: a product wins if no product of the same category has a strictly higher average. Comparing b.total * s.reviews > s.total * b.reviews is the same as comparing the averages but in exact integers. Both cost one aggregation over the reviews plus a sort (or a self-comparison) per category; categories are small, so either is cheap.

Reference solution (MySQL)

WITH scored AS (
  SELECT p.category, p.product_name, AVG(r.rating) AS avg_raw, COUNT(*) AS reviews
  FROM products p
  JOIN product_reviews r ON r.product_id = p.product_id
  GROUP BY p.product_id, p.category, p.product_name
  HAVING COUNT(*) >= 3
), ranked AS (
  SELECT category, product_name, avg_raw, reviews,
         RANK() OVER (PARTITION BY category ORDER BY avg_raw DESC) AS rnk
  FROM scored
)
SELECT category, product_name, ROUND(avg_raw, 2) AS avg_rating, reviews
FROM ranked
WHERE rnk = 1
ORDER BY category, product_name

Another way

WITH scored AS (
  SELECT p.category, p.product_name, SUM(r.rating) AS total, COUNT(*) AS reviews
  FROM products p JOIN product_reviews r ON r.product_id = p.product_id
  GROUP BY p.product_id, p.category, p.product_name HAVING COUNT(*) >= 3
)
SELECT s.category, s.product_name, ROUND(s.total / s.reviews, 2) AS avg_rating, s.reviews
FROM scored s
WHERE NOT EXISTS (SELECT 1 FROM scored b WHERE b.category = s.category AND b.total * s.reviews > s.total * b.reviews)
ORDER BY s.category, s.product_name

← Sellers With a High Return Rate · Customers Whose First Order Used a Coupon →