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

select * from
(
select Customer, 'Prod' + cast( row_number() OVER (partition by customer order by time) as varchar(2)) as ProductName, Product from transactions
) A
PIVOT
(
sum(product) for ProductName in (
[Prod1],[Prod2],[Prod3],[Prod4],[Prod5],[Prod6])
) B

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

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s