## 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