SQL Advanced level Question 2
Question2: Find the product with the highest average rating, and return the product ID, average rating, and the number of reviews for that product. If there are multiple products with the highest average rating, return all of them.
| review_id | product_id | customer_id | review _text | rating | review _date |
|---|---|---|---|---|---|
| 1 | 101 | 1 | Great product, really loved it! | 5 | 2025-01-15 12:00:00 |
| 2 | 102 | 2 | Not as expected, quality could be better. | 3 | 2025-01-15 12:05:00 |
| 3 | 101 | 3 | Excellent value for money. Highly recommended! | 5 | 2025-01-15 12:10:00 |
| 4 | 103 | 4 | The item arrived broken. Disappointed. | 1 | 2025-01-15 12:15:00 |
| 5 | 102 | 5 | Okay product, works as described. | 4 | 2025-01-15 12:20:00 |
| 6 | 101 | 6 | Fantastic quality and fast shipping! | 5 | 2025-01-15 12:25:00 |
| 7 | 104 | 7 | Very poor design, uncomfortable to use. | 2 | 2025-01-15 12:30:00 |
| 8 | 103 | 8 | Good product but needs improvement. | 3 | 2025-01-15 12:35:00 |
| 9 | 105 | 9 | Absolutely terrible, do not buy! | 1 | 2025-01-15 12:40:00 |
| 10 | 101 | 10 | Solid product, works great. | 4 | 2025-01-15 12:45:00 |
WITH product_avg_ratings AS (
SELECT
product_id,
AVG(rating) AS avg_rating,
COUNT(review_id) AS review_count
FROM product_reviews
GROUP BY product_id
),
max_avg_rating AS (
SELECT
MAX(avg_rating) AS highest_avg_rating
FROM product_avg_ratings
)
SELECT
p.product_id,
p.avg_rating,
p.review_count
FROM product_avg_ratings p
JOIN max_avg_rating m ON p.avg_rating = m.highest_avg_rating
ORDER BY p.product_id;
Explanation:
- CTE
product_avg_ratings: This Common Table Expression calculates the average rating (avg_rating) and the total number of reviews (review_count) for each product. - CTE
max_avg_rating: This CTE finds the highest average rating across all products. - Final SELECT Query: It joins the two CTEs to fetch the
product_id,avg_rating, andreview_countfor the products that have the highest average rating. The results are ordered byproduct_idto make it more readable.
Sample Output:
If, for example, products101 and 102 have the highest average ratings, the result could look like this:
| product_id | avg_rating | review_count |
|---|---|---|
| 101 | 4.8 | 5 |
| 102 | 4.0 | 2 |
