Find the Average Rating of Each Product

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The product team is building a Customer Ratings section on each product page. Calculate the average rating for every product with at least one review.

Tables

Table: products

| product_id | product_name | category_id | stock_quantity |
| --- | --- | --- | --- |
| 1 | T-Shirt | 1 | 50 |
| 2 | Jeans | 1 | 0 |
| 3 | Sneakers | 2 | 30 |
| 4 | Backpack | 2 | 0 |
| 5 | Watch | 3 | 15 |
| 6 | Sunglasses | 3 | 10 |

Table: categories

| category_id | category_name |
| --- | --- |
| 1 | Apparel |
| 2 | Footwear |
| 3 | Accessories |

Table: order_items

| item_id | product_id | quantity | unit_price | order_date |
| --- | --- | --- | --- | --- |
| 1 | 1 | 5 | 599 | 2024-05-01 |
| 2 | 3 | 2 | 2499 | 2024-05-01 |
| 3 | 2 | 3 | 1299 | 2024-05-02 |
| 4 | 5 | 1 | 1499 | 2024-05-02 |
| 5 | 3 | 4 | 2499 | 2024-05-03 |
| 6 | 1 | 2 | 599 | 2024-05-03 |
| 7 | 2 | 1 | 1299 | 2024-06-01 |
| 8 | 4 | 2 | 899 | 2024-06-01 |
| 9 | 5 | 4 | 1499 | 2024-06-02 |

Table: product_reviews

| review_id | product_id | rating |
| --- | --- | --- |
| 1 | 1 | 5 |
| 2 | 1 | 4 |
| 3 | 1 | 3 |
| 4 | 2 | 4 |
| 5 | 2 | 3 |
| 6 | 3 | 5 |
| 7 | 3 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 4 |
| 10 | 5 | 5 |

Expected Output

| product_id | product_name | avg_rating |
| --- | --- | --- |
| 3 | Sneakers | 5.00000 |
| 5 | Watch | 4.50000 |
| 1 | T-Shirt | 4.00000 |
| 2 | Jeans | 3.50000 |
| 4 | Backpack | 2.00000 |

Return: product_id, product_name, average_rating (rounded to 2 decimal places)
Only products with at least one review (product 3 not shown)
Sort by average_rating descending