Getting probability distributions of items within a certain time range from a different table

user_12342251 0
Viewed 25

I have two tables 'PURCHASE' and 'CART' shown below.

PURCHASE

| time       | campaign_id | purchase_id |
| ---------- | ----------- | ----------- |
| 2021-08-13 | a           | 111         |
| 2021-08-11 | b           | 222         |

CART

| time       | campaign_id | cart_id |
| ---------- | ----------- | ------- |
| 2021-08-12 | b           | v       |
| 2021-08-10 | a           | w       |
| 2021-08-09 | a           | x       |
| 2021-08-04 | b           | y       |
| 2021-08-02 | a           | z       |

Since I don't have the means to connect which cart event led to which purchase event, I want to create the following table, which gives the probability that one cart event led to a purchase event. I only want to include cart events that occurred within X days (in this case, 10 days) before purchase, and the campaign_id must match:

| time       | campaign_id | purchase_id | cart_id | probability |
| ---------- | ----------- | ----------- | ------- | ----------- |
| 2021-08-13 | a           | 111         | w       | 0.5         |
| 2021-08-13 | a           | 111         | x       | 0.5         |
| 2021-08-11 | b           | 222         | y       | 1.0         |

I have tried making a sub-table (like below) with the minimum and maximum times, and played around with the rank() function but have not been able to get the above result.

| time_min   | time_max   | campaign_id | purchase_id |
| ---------- | ---------- | ----------- | ----------- |
| 2021-08-03 | 2021-08-13 | a           | 111         |
| 2021-08-01 | 2021-08-11 | b           | 222         |

If you have a solution for this, I would appreciate your help.

Thank you in advance.

mysql sql
1 Answers

You can do a join on campaign_id using the time range. Then count the number of matches. Your probability seems to be 1 divided by this count.

select p.*, c.cart_id,
       (1.0 / count(*) over (partition by p.purchase_id)) as probability
from purchase p join
     cart c
     on p.campaign_id = c.campaign_id and
        c.time <= p.time and
        c.time > p.time - interval 10 day;
user_11440350
Related