logo

Back to questions

Average Review Ratings

Easy

Given the reviews table, write a query to get the average stars for each product every month.

The output should include the month in numerical value, product id, and average star rating rounded to two decimal places. Sort the output based on month followed by the product id.

P.S. If you've read the Ace the Data Science Interview, and liked it, consider writing us a review?

Table:

Column NameType
review_idinteger
user_idinteger
submit_datedatetime
product_idinteger
starsinteger (1-5)

Example Input:

review_iduser_idsubmit_dateproduct_idstars
617112306/08/2022 00:00:00500014
780226506/10/2022 00:00:00698524
529336206/18/2022 00:00:00500013
635219207/26/2022 00:00:00698523
451798107/05/2022 00:00:00698522

Example Output:

mthproductavg_stars
6500013.50
6698524.00
7698522.50

Explanation

In June (month #6), product 50001 had two ratings - 4 and 3, resulting in an average star rating of 3.5.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.