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.
![](https://cdn-images-1.medium.com/max/1600/1*nTgPaoT6d-UgrQi4OxRBcw.png)
![](https://cdn-images-1.medium.com/max/1600/1*Y4ziJlpujkoF6HXJLh0-aQ.png)
- 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.
![](https://cdn-images-1.medium.com/max/1600/1*1RFJT5oFlqCZooMjX5b9mQ.png)
- 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
![](https://cdn-images-1.medium.com/max/1600/1*7K91eyKFuDLjIBtyYJTfWw.png)
Submission
- Our solution is accepted by the platform.
![](https://cdn-images-1.medium.com/max/1600/1*24S2DtJ9Kf4N4qh8JRZ09w.png)
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
Bonus Tip
- If you want to upskill your SQL interview game, you can definitely check out this bestseller course