Retrieve the average star rating for each product, grouped by month.
Table Schema
| Column | Type | Description |
|---|---|---|
| review_id | integer | Unique review identifier. |
| user_id | integer | User who submitted the review. |
| submit_date | datetime | Timestamp when the review was submitted. |
| product_id | integer | Reviewed product identifier. |
| stars | integer | Star rating from 1 to 5. |
Sample Data
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 6171 | 123 | 06/08/2022 00:00:00 | 50001 | 4 |
| 7802 | 265 | 06/10/2022 00:00:00 | 69852 | 4 |
| 5293 | 362 | 06/18/2022 00:00:00 | 50001 | 3 |
| 6352 | 192 | 07/26/2022 00:00:00 | 69852 | 3 |
| 4517 | 981 | 07/05/2022 00:00:00 | 69852 | 2 |
| mth | product | avg_stars |
|---|---|---|
| 6 | 50001 | 3.50 |
| 6 | 69852 | 4.00 |
| 7 | 69852 | 2.50 |
SQL Editor
| mth | product | avg_stars |
|---|---|---|
| 6 | 50001 | 3.50 |
| 6 | 69852 | 4.00 |
| 7 | 69852 | 2.50 |
Hints
Solution
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT EXTRACT(MONTH FROM submit_date) AS mth, product_id AS product, ROUND(AVG(stars), 2) AS avg_stars FROM reviews GROUP BY EXTRACT(MONTH FROM submit_date), product_id ORDER BY mth, product;
Explanation
Extract the numeric month from submit_date so reviews can be grouped by month.
Group by that month and product_id, then average the stars for each group.
Round the average to two decimal places and sort by month, then product.
Related Questions
