Query – Which product customer bought right after a purchase

Lets assume we have this cvs data (scroll below). It shows customerID, ProductID and the time the product was purchased. We are required to figure out which product customer bought right after a purchase . We want the output in this form.

purchase history

We are most likely going to useĀ  SQL PIVOT but the output form cannot be directly obtained by simply PIVOTING the data. The reason being each product will have it own column in the pivot table which we do not want (will be too much to digest the data). Here is the query that will do the trick. It uses PIVOT but it also uses row_number() and assigned a psuedo prod names to each product (prod1, prod2, prod3,… and so on) which simplies the pivoting

The result output is this

pivot output

 

CVS Data to play with

You can import this into SQL Server using the import feature in SSMS. Make sure you change the product column to int type after import (by default it will be stored in varchar)

Customer,Product,Time
76815823,41624,58:44.0
32442296,770524,18:43.0
16560010,77575,21:04.0
76815823,771532,43:17.0
108152308,772542,19:01.0
108152308,772542,53:38.0
226005589,772348,11:31.0
108152308,772543,10:58.0
85526986,772614,53:14.0
16010767,770095,16:55.0
201300728,772348,24:22.0
226005589,770861,21:48.0
201300728,770406,27:23.0
226005589,77496,30:04.0
16010767,23059,25:28.0
187686099,200677,43:20.0
16010767,772552,41:44.0
16010767,770476,47:42.0
16010767,772295,50:41.0
16010767,772543,53:17.0
16010767,772184,57:20.0
16010767,772183,59:59.0
164662752,100259,02:38.0
187686099,771606,10:01.0
168439174,200322,21:11.0
187686099,200497,23:17.0
108152308,772544,43:27.0
190017804,771984,30:46.0
16010767,772182,05:34.0
190017804,77975,41:34.0
168439174,200678,41:06.0
16010767,772351,41:45.0

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*