There are 3 tables in my dataset. In Table 1, there are buyers of innovative products: purchase month, product_id, IdCustomer. In Table 2, we have all the information related to the products: product_id, brand, category. Table 3 contains all orders that include products, not just innovative products: order date, product_id, customerID, price. I want to create a table: for each reference month (from Table 1) and for each purchased innovative product_id, the number of customers who purchased the category corresponding to that innovative product in the three months preceding the reference month, the revenue from customers who purchased the category corresponding to that innovative product in the three months preceding the reference month, and these two indicators for the six months following the reference month.
tab 1 :
purchase month | product_id | customer_id |
---|---|---|
2024-04 | 1234 | Emma |
2024-04 | 1235 | Leo |
2024-04 | 1239 | Emma |
2024-04 | 1239 | Hugo |
2024-05 | 1234 | Thomas |
2024-05 | 4521 | Emma |
2024-05 | 3976 | Brice |
2024-05 | 1235 | Hugo |
tab 2 :
product_id | brand | category |
---|---|---|
1234 | coca | Groserie |
1235 | lays | Groserie |
1239 | Fererro | cream |
4521 | Fererro | Cream |
3976 | Fererro | Groserie |
tab 3 :
purchase month | product_id | customer_id | price |
---|---|---|---|
2024-01-23 | 1234 | Emma | 5 |
2024-03-03 | 1235 | Leo | 2 |
2024-04-01 | 1239 | Emma | 1 |
2024-04-03 | 1239 | Hugo | 1.5 |
2024-05-27 | 1234 | Thomas | 3 |
2024-02-09 | 4521 | Max | 2 |
2024-06-22 | 3976 | Brice | 7 |
2024-07-10 | 1235 | Hugo | 6 |
I want a Output like :
purchase month | product_id | nb customer | turnover |
---|---|---|---|
2024-04 | 1234 | XX | XX |
2024-04 | 1235 | XX | XX |
2024-04 | 1239 | XX | XX |
2024-05 | 1234 | XX | XX |
2024-05 | 4521 | XX | XX |
2024-05 | 3976 | XX | XX |
2024-05 | 1235 | XX | XX |
for instance : Emma only bought cream in May, She should not be considered a grocery buyer for the month of May.
You need to sign in to view this answers
Leave feedback about this