Back to questions

Average Review Ratings Amazon SQL Interview Question

Average Review Ratings

Amazon SQL Interview Question

Given the reviews table, write a query to retrieve the average star rating for each product, grouped by month. The output should display the month as a numerical value, product ID, and average star rating rounded to two decimal places. Sort the output first by month and then by 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

Product 50001 received two ratings of 4 and 3 in the month of June (6th month), resulting in an average star rating of 3.5.

The dataset you are querying against may have different input & output - this is just an example!

Want to try other Amazon SQL Interview Questions?

Here's some more Amazon SQL Interview Questions: Amazon SQL Interview Questions

Input

Output