SQL - calculating overlap of products bought together by customers

user_16767382 0
Viewed 219

I am using SQL. My database looks like this:

Customer_ID Product_ID Number of purchased Product_ID
1 x 1
1 y 6
2 x 15
2 z 1
3 y 1
3 z 1
3 x 5
4 x 2
4 y 1

I want to find how often the same product combination is bought by the customers. To Calculate Times_Bought_Together_%: count of Product_ID_1 in the first column is divided by the total count of customers of both. This means that the Number of purchased Product_ID has to be ignored as the calculation is made on the #customer_ID level and not #product_ID level

In this example, combination xy has been bought by 3 out of 4 (Customer_ID = 1,2,3,4), combination zx by 1 out of 2 (Customer_ID = 2,3), combination yz 1 out of 3 (Customer_ID = 1,3,4)

the result should show something like this:

Product_ID_1 Bought_With_Product_ID_2 Times_Bought_Together_in_%
x y 75%
z x 50%
y z 33%

I used a self-join and window functions to calculate this:

   select a.product_id, b.product_id,
          count(*) as times_bought_together,
          count(*) * 1.0 / cnt as ratio
    
    from (select t.*, count(*) over (partition by product_id) as cnt
              from t
             ) a join
             t b
             on b.customer_id = a.customer_id and
                b.product_id != a.product_id 
    group by a.product_id, b.product_id, a.cnt;

however instead of counting each row, the count( * ) function counts the total Number of purchased product_ID for each row which I am not interested in. How could I change the count( * ) in my ratio function to calculate what I am looking for?

sql
1 Answers

If you don't want something in the select list, then just don't include it:

select a.product_id, b.product_id,
       CAST(count(*) * 1.0 / cnt AS DECIMAL(10,2)) as ratio
from (select t.*, count(*) over (partition by product_id) as cnt
      from t
     ) a join
     t b
     on b.customer_id = a.customer_id and
        b.product_id <> a.product_id and 
        a.product_id < b.product_id
group by a.product_id, b.product_id, a.cnt;

Note: Your results suggest that you actually want a.product_id < b.product_id.

user_11440350
Related