Blog

Introduction

• In this article we will solve Highest-Grossing Items SQL question which is asked by Amazon as per DataLemur website.

If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.

Question

• We have been given input table that consist of product_spend of amazon customer on various categories.
• We need to identify top two grossing products within each category in 2022.

• As we can see that category appliance and electronics with their top two grossing product along with total spend as output.

Solution

• At first we will filter all the transactions done in 2022. We will group by category and product and do the sum of spend to find out total expenditure in each category and product.

• Now in previous step we have each category and product with total_spend.
• Now all we have to do is to rank these records within each category based on the total_spend.
• We will use windowing operation to partition based on category and order by total_spend in descending order to get higher spend as first in order.

• As we can see that appliance category one has two items which is was expected. for electronics we have 3 products so we only need to return top 2.

• Now we can perform filtering by rank where rank is less than 3

Final Solution

• Here is the final solution for this problem.

https://asyncq.com/media/e8290c22f2b35e0c29923de64d8e80b6

Submission

• Our solution is accepted by the platform.

Conclusion

• In this article we solved SQL question asked by Amazon. We used Windowing operation to rank the product based on total_spend. We also used subquery to form base table for querying output.

Bonus Tip

• If you want to upskill your SQL interview game, you can definitely check out this bestseller course