Top Rated Products by Category
Which product categories have an average customer rating above 4.0, and how many distinct products in each category have been reviewed?
- Joins
- Aggregation
- HAVING
- Numeric functions
- Sorting
Challenge brief
Understand the request
Product Quality Team wants to surface high-quality product lines and needs to know which categories consistently receive strong ratings.
Find product categories with average rating above 4.0, showing distinct product count and average rating.
Return
- category
- product_count (distinct reviewed products)
- avg_rating (rounded to 2 decimals)
Constraints
- Only include categories whose average review rating is greater than 4.0
- Count each reviewed product once even when it has multiple reviews
- Return the highest-rated category first
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
products
product_idINTEGERcategoryVARCHAR(100)
reviews
product_idINTEGERratingINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Product category is in products. Ratings are in reviews. Connect them on product_id. You need to group by category, count distinct products reviewed, and average the ratings.
Hint 2
INNER JOIN products to reviews on product_id. GROUP BY p.category. Use COUNT(DISTINCT p.product_id) AS product_count and ROUND(AVG(r.rating), 2) AS avg_rating. Filter groups with HAVING AVG(r.rating) > 4.0.
Hint 3
Scaffold: group joined product-review rows by category; project a distinct product count and rounded rating average before filtering qualified groups.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT p.category, COUNT(DISTINCT p.product_id) AS product_count, ROUND(AVG(r.rating), 2) AS avg_rating FROM products p INNER JOIN reviews r ON p.product_id = r.product_id GROUP BY p.category HAVING AVG(r.rating) > 4.0 ORDER BY avg_rating DESC;Why this works
HAVING filters after grouping — it is the GROUP BY equivalent of WHERE. COUNT(DISTINCT product_id) prevents double-counting when a product has multiple reviews. Sports (rating 3.0) is excluded. Books has no reviews and does not appear.
Success check
2 categories — Electronics (4.8) and Home & Kitchen (4.33)
Expected result
Use this output to verify values, aliases, ordering, and row count.
| category | product_count | avg_rating |
|---|---|---|
| Electronics | 4 | 4.8 |
| Home & Kitchen | 2 | 4.33 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Uber
Independent Uber-style mobility marketplace SQL practice covering trips, drivers, riders, pricing, payments, and promotions.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Return to the complete interview preparation experience.