SQLTeam.com | Weblogs | Forums

For each product purchased, what were the other products purchased?

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

hi

does this help :slight_smile:

;WITH cte
AS (
       SELECT
            ROW_NUMBER() OVER (PARTITION BY
                                   customerid
                               ORDER BY
                                   productid) AS rn
            , *
       FROM
            #data
   )
SELECT
    a.productid
    , COUNT(*)
FROM
    cte a
    JOIN (   SELECT
                    productid
                    , customerid
             FROM
                    cte
             WHERE
                 rn = 1) b
        ON a.customerid = b.customerid
           AND  a.productid <> b.productid
GROUP BY
    a.productid;