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
Bonus Tip
- If you want to upskill your SQL interview game, you can definitely check out this bestseller course