Bigquery SQL to ensure that only customers who purchased the relevant category in the specified time frame are included

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 :

tab 2 :

tab 3 :

I want a Output like :

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