Purchasing Activity by Product Type — Amazon Asked SQL Question

  • Post last modified:September 10, 2022
  • Reading time:2 mins read
Photo by Caspar Camille Rubin on Unsplash

Introduction

  • In this article we will solve Purchasing Activity by Product Type 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 purchasing activity table and we need to find out cumulative purchases of each product over time.
  • As we can see in the output printer total cum_purchased count is 20 for the first purchase , for second purchase its 20+18 = 38. So basically we need to add total quantity purchased until previous date.

Solution

  • We will partition our input table by product_type which will be based on sorted order of order_date column.
  • Once we have partition , we can perform sum over quantity . Each window function has frame clause , and default frame is range unbounded preceding and current row, this is perfect for our scenario since we want to cumulative sum which matches with default frame definition.
  • So once we do the sum operation over defined partition we will get the cumulative sum for the each product .
select order_date,product_type, 
sum(quantity) over(partition by product_type order by order_date) cum_purchased 
from total_trans
  • Here in below output , each hair dryer is added with previous calculation of quantity for particular product type based on order_date.
  • As to match the final output we need to order the entire output based on order_date, currently only each partition is order by order_date not the entire table.
select order_date,product_type, 
sum(quantity) over(partition by product_type order by order_date) cum_purchased 
from total_trans
order by order_date

Submission

  • Our solution is accepted by the platform.

Conclusion

  • In this article we solved sql question asked by Amazon , we used sql windowing operation and understood the default operating frame in sql window operation.
  • There are many window operation, checkout this blog

Leave a Reply