Monthly Product Review RatingsCompany: Amazon | Difficulty: easy

Amazon wants to monitor product ratings over time.

Write a query to calculate the average star rating for every product in each month of 2025. Return the month as a number (month), the product_id, and the average star rating rounded to two decimal places as avg_stars.

Sort the results by month in ascending order, followed by product_id in descending order.

Table Schema

Table	Column	Type
`reviews`	`review_id`	integer
`reviews`	`user_id`	integer
`reviews`	`submit_date`	timestamp
`reviews`	`product_id`	integer
`reviews`	`stars`	integer

Example Input: `reviews`

review_id	user_id	submit_date	product_id	stars
1001	201	2025-01-05 10:00:00	70001	5
1002	202	2025-01-12 14:30:00	70002	4
1003	203	2025-01-20 09:15:00	70001	3
1004	204	2025-02-02 16:00:00	70002	2
1005	205	2025-02-14 11:45:00	70002	4
1006	206	2025-02-20 18:20:00	70003	5

Example Output Explanation

Product 70001 has two reviews in January with an average rating of 4.00. Product 70002 has one January review averaging 4.00 and two February reviews averaging 3.00. Product 70003 has one February review with a rating of 5.00. Because of the descending tie-breaker on product_id, 70002 appears before 70001 in January, and 70003 appears before 70002 in February.

SchemaDROP TABLE IF EXISTS reviews; CREATE TABLE reviews ( review_id INT, user_id INT, submit_date TIMESTAMP, product_id INT, stars INT ); INSERT INTO reviews VALUES (1001, 201, TIMESTAMP '2025-01-05 10:00:00', 70001, 5), (1002, 202, TIMESTAMP '2025-01-12 14:30:00', 70002, 4), (1003, 203, TIMESTAMP '2025-01-20 09:15:00', 70001, 3), (1004, 204, TIMESTAMP '2025-02-02 16:00:00', 70002, 2), (1005, 205, TIMESTAMP '2025-02-14 11:45:00', 70002, 4), (1006, 206, TIMESTAMP '2025-02-20 18:20:00', 70003, 5);← All SQL Challenges · Amazon Questions · Learn SQL Free