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
| Column | Type |
|---|---|
| product_id | int |
| product_name | varchar |
| category | varchar |
Primary key: product_id.
One row per listed product; names are unique.
Table: product_reviews
| Column | Type |
|---|---|
| review_id | int |
| product_id | int |
| customer_id | int |
| rating | int |
Primary key: review_id.
One row per review; rating is 1–5 stars. product_id is always in products.
Examples
Example 1
products
| product_id | product_name | category |
|---|---|---|
| 1 | Galaxy M34 | Mobiles |
| 2 | Moto G54 | Mobiles |
| 3 | Nord CE 3 | Mobiles |
| 4 | Silk Saree | Fashion |
| 5 | Linen Shirt | Fashion |
| 6 | Mixer Grinder | Home |
product_reviews
| review_id | product_id | customer_id | rating |
|---|---|---|---|
| 1 | 1 | 11 | 5 |
| 2 | 1 | 12 | 4 |
| 3 | 1 | 13 | 4 |
| 4 | 2 | 14 | 5 |
| 5 | 2 | 15 | 5 |
| 6 | 3 | 16 | 3 |
| 7 | 3 | 17 | 5 |
| 8 | 3 | 18 | 4 |
| 9 | 3 | 19 | 5 |
| 10 | 3 | 20 | 5 |
| 11 | 3 | 21 | 4 |
| 12 | 4 | 22 | 5 |
| 13 | 4 | 23 | 4 |
| 14 | 4 | 24 | 3 |
| 15 | 5 | 25 | 4 |
| 16 | 5 | 26 | 4 |
| 17 | 5 | 27 | 4 |
| 18 | 6 | 28 | 2 |
| 19 | 6 | 29 | 5 |
Output
| category | product_name | avg_rating | reviews |
|---|---|---|---|
| Fashion | Linen Shirt | 4 | 3 |
| Fashion | Silk Saree | 4 | 3 |
| Mobiles | Galaxy M34 | 4.33 | 3 |
| Mobiles | Nord CE 3 | 4.33 | 6 |
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_nameAnother 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 →