Creating a Pivot Table using SQL to Track Customer Purchases and Expenses over a 3 Month Period

Question

I want to create a table that counts the number of purchases and expenses made by a customer on my products. I am creating a pivoted table in SQL with the following columns:

• execution_date: the date on which my query will run to scan the database for the past 3 months
• client_id: the identifiers of all my customers in the past 3 months
• purchased_product (boolean multiplied by the number of products in my database): False when the customer has not purchased my product in the past 3 months
• product_expenses (boolean multiplied by the number of products in my database): the cumulative sum of expenses made by the customer on my products in the past 3 months
• product_quantity (boolean multiplied by the number of products in my database): the cumulative sum of the quantity of units purchased by the customer in the past 3 months

The idea is to create a kind of "history" that traces the activity of a customer over the past 3 months at a certain period. To do this, I am using:

• A sales table: sales
• A product reference table: product
• The GENERATE_DATE_ARRAY function: especially to calculate purchases made before the creation of my table to retrieve the history

Here is an example:

My customer A registered in my database on 2017-02-12 and did not make any purchases in the first 3 months:

• On 2017-05-20, he purchases a product X for $35 and a quantity of 2 units.
• On 2018-01-16, he repurchases the same product for the same quantity for $34.
• On 2018-02-10, he purchases the **product Y **for the first time for $60 and a quantity of 3 units.

Here is an example of the desired output.

I tried this approach :

 WITH
 SH AS (
  SELECT
    client_id,
    datepurchase,
    product_id,
    item_quantity,
    expenses

  FROM sales, UNNEST(sales.lines) AS l
  
  WHERE valid_purchase
    AND client_id = 'A'
)

, final AS (
SELECT 
  s.* 
  , classification_axis AS product_name
  , COUNT(DISTINCT ocdTicketOrOrderId) OVER (PARTITION BY ocdMasterId) AS orders

  
FROM sales AS s
  LEFT JOIN product AS p ON s.eanCode = p.variantInfo.eanCode

WHERE TRUE
  AND classification_axis IS NOT NULL
  AND client_id = 'A'
)

SELECT 
  p.*
FROM (
  SELECT
    dates
  , product_name
  , SUM(IF(itemUnitPriceTaxIncludedAfterDiscount IS NULL, 0, itemUnitPriceTaxIncludedAfterDiscount)) AS price

FROM final 
  RIGHT JOIN `data-crm-france.bucket_crm.fr_dcr_dates` AS d ON DATE(d.dates) = DATE_TRUNC(DATE(final.datepurchase), MONTH)

  WHERE dates >= DATE('2021-01-01')

GROUP BY ALL 
)
  PIVOT (SUM(IF(price IS NULL, 0, price)) FOR product_name IN ('X', 'Y')) AS p

The approach is not providing the desired results as it includes null values and does not calculate the cumulative sum