Mastering SQL for Average Product Ratings: Amazon Interview Question

Understanding review data in an effective manner for data analysis and SQL can be really great at providing insights about the performance of your products and the satisfaction of your customers. Here’s a common SQL interview question on calculating average ratings for reviews on products, by month. It is therefore important to know this query so you might understand how trends are moving and how to evaluate performance over time.

1. The Scenario

You are given a reviews table that is similar to one below:

• review_id: Unique ID for review
• user_id: Unique ID for the user who made the review
• submit_date: Date and time when the review was made
• product_id: Unique ID for the product being reviewed
• stars: Rating made by the user ranging from 1 to 5.

Your task is to write an SQL query that fetched the average star rating for each product. The output should have the following columns:

1. Month as a number
2. Product ID
3. Average star rating, rounded to two decimal places

The results should be sorted first by month and then by product ID .

2. Building the SQL Query

To Achieve your final result, you need to follow the below steps:

• Extract the Month from the submit_date: Use SQL date functions to extract the month from the submit_date field.
• Calculate the Average Rating: Use the GROUP BY clause with both month and product ID to calculate the average star rating.
• Round the Average Rating: Round the average rating to two decimal places.
• Sort the Results: Order the results first by month and then by product ID.

First lets create the reviews table

CREATE TABLE reviews ( 
review_id INTEGER, 
user_id INTEGER, 
submit_date DATETIME, 
product_id INTEGER, 
stars INTEGER, 
name_id INTEGER, 
mth INTEGER 
);

3. Insert Sample Data into your reviews table:

INSERT INTO reviews (review_id, user_id, submit_date, product_id, stars, name_id, mth) VALUES
(6171, 123, '2022-06-08 00:00:00', 50001, 4, NULL, NULL),
(7802, 265, '2022-06-10 00:00:00', 69852, 4, NULL, NULL),
(5293, 362, '2022-06-18 00:00:00', 50001, 3, NULL, NULL),
(6352, 192, '2022-07-26 00:00:00', 69852, 3, NULL, NULL),
(4517, 981, '2022-07-05 00:00:00', 69852, 2, NULL, NULL),
(2501, 142, '2022-06-21 00:00:00', 12580, 5, NULL, NULL),
(4582, 562, '2022-06-15 00:00:00', 12580, 4, NULL, NULL),
(2536, 136, '2022-07-04 00:00:00', 11223, 5, NULL, NULL),
(1200, 100, '2022-05-17 00:00:00', 25255, 4, NULL, NULL),
(1301, 120, '2022-05-18 00:00:00', 25600, 4, NULL, NULL),
(2555, 232, '2022-05-31 00:00:00', 25600, 4, NULL, NULL),
(2556, 167, '2022-05-31 00:00:00', 25600, 5, NULL, NULL);

4. Write a SQL Query

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_id;

5. Detailed Explanation

• EXTRACT(MONTH FROM submit_date): Retrieves the month from the submit_date field.
• ROUND(AVG(stars), 2): It computes the average stars rating, then rounds up to two decimal places.
• GROUP BY EXTRACT(MONTH FROM submit_date ), product_id : Aggregates data on a by-month and by the ID of the product basis
• ORDER BY mth, product_id : Orders outputted results first by month and then by the product ID.

As conclusion, take your SQL skills to the next level using our intensive SQL Master Package, which contains revision notes, case studies, and intelligence on the implementation of multinational corporations’ SQL practices. This package is designed to hone your SQL skills and prepare you for advanced data analysis challenges, putting your SQL skills to task.

6. Conclusion

This query provides an extremely clear overview of just how the products are rated, on average, every month, which can be very valuable for trends analysis, knowing which products perform best, and understanding what the customers want.

Mastering such queries can really amplify your data analysis skills and forms an integral part of many data-related roles.

Hope this tutorial was some help. If so, do consider giving us some feedback. Your support will help us to create more valuable content.

Happy querying!

For more information about SQL, check out my earlier blog: Active User Retention: Facebook SQL Interview Question