I had a business problem, where we had the purchase data from a website with login and the products that was purchased for last one year. My manager wanted me to just keep the latest purchase date for every login and only 3 products and delete the rest. He told i can pick any 3. May be at a later stage they want to do it based on amount of the product purchased.
I was thinking about the solution for this and thought I will create a logic in such a way that there will be a sequence id for every login and then, delete the ones above 3, so I will end up with 3 products per login. I was thinking of doing with cursor or while loop, but it will be a costly operation, so I was looking around and found this cool -- partition, Row_number() and order by combination that will do it.
I have a sample of what I did here.
The query to create the sequence is:
No comments:
Post a Comment