Objective

Write a SQL query to calculate the average star rating per product by month from a product reviews table.

Table Schema:

• product_reviews

Task Requirements:

• Group reviews by month and product
• Calculate the average star rating for each product in each month
• Round the average rating to the nearest integer
• Sort results by month and then by product ID
  
  

Output columns: review_month, product_id, avg_rating

SELECT 
    DATE_TRUNC('month', review_date) AS review_month,
    product_id,
    ROUND(AVG(star_rating)) AS avg_rating
FROM product_reviews
GROUP BY DATE_TRUNC('month', review_date), product_id
ORDER BY review_month ASC, product_id ASC;

Explanation

Step 1: Truncate Review Date to Month

Use DATE_TRUNC('month', review_date) to group all reviews from the same month together, returning the first day of each month.

DATE_TRUNC('month', review_date) AS review_month

Step 2: Group by Month and Product

Group by both the truncated month and product_id so ratings are averaged per product per month.

GROUP BY DATE_TRUNC('month', review_date), product_id

Step 3: Calculate Rounded Average Rating

Use AVG(star_rating) to compute the mean and ROUND() to round to the nearest integer.

ROUND(AVG(star_rating)) AS avg_rating

Step 4: Sort Results

Order by review_month ascending, then by product_id ascending within each month.

ORDER BY review_month ASC, product_id ASC