Best-Selling Product [Amazon SQL Interview Question]

Write an SQL query to find the best-selling product in each product category. If there are two or more products with the same sales quantity, go by whichever product which has the higher review rating.

Return the category name and product name in alphabetical order of the category.

Table:

Example Input:

Table:

Example Input:

Example Output:

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

First, we join the products table on the corresponding product id with the product_sales table to operate on category name, product name, rating and sales quantity fields together.

The best-selling product is the product with the highest quantity of units sold. So, we rank the products in each category by their sales quantity. This can be achieved by the usage of window function.

Read here and here to learn more about and window function, respectively.

You will get the (3 rows in random order) of output below:

If you look at the output, you will notice products Refrigerator and Dishwasher are both ranked first in Home Appliances category. It's because the same quantity of units (70 units) for both products were sold.

In such cases, we must obtain the product with the higher rating, according to the question guidelines. It can be done by adding a rating column in the clause and specify to get the higher rating first.

The output will look like this, and you will be able to see what we just talked about.

Then, we will convert the query into a subquery and get the products with first ranking in the clause. At the end, add an clause to sort the result based on category name in alphabetical order.

Results:

Solution #1: Using Subquery

Solution #2: Using CTE