Hello,
I need to create a dataset which tells me for each product, which other products were bought. The structure I have in my head would be to have the one field for original product bought, the other product bought and then how many times (based on customerID) this was purchased. I would then use this number to rank the other products for each original product. I am not sure how to achieve this using SQL and I am wondering whether I might need to use another tool.
Does anyone have any advice please?
My example data is:
ProductID | ProductName | CustomerID | SaleDate |
---|---|---|---|
1001 | Paper | 234 | 18/05/2020 |
1002 | Pencil | 234 | 18/05/2020 |
1003 | Sharpener | 234 | 18/05/2020 |
In the above example, when looking at Paper I would see the Pencil and Sharpener were also purchased. I wouldn't want this at the customer level though, I just need to see the other product and a number.
Thanks in advance