Is there any way to reset sum cumulative / running total every month in BigQuery?-CodePudding

I want to calculate a month-to-date profit with the current date 15th, so it will sum the profit every month until the 15th day. Is there any way/idea to reset the sum cumulative / running total every month in bigquery? i want to filter the windows function so that every 1st day in the month will reset the sum cumulative in the profit_cumulative column.

so I want the result to be like this :

Date	Categories	Profit	Profit_Cumulative
2022-06-14	A	295.62	6350.58
2022-06-15	A	459.80	6810.38
2022-07-01	A	501.03	501.03
2022-07-02	A	258.97	760.0

instead of this:

Date	Categories	Profit	Profit_Cumulative
2022-06-14	A	295.62	6350.58
2022-06-15	A	459.80	6810.38
2022-07-01	A	501.03	7311.72
2022-07-02	A	258.97	7570.69

and this is my code :

  b AS (
  WITH
    a AS (
    SELECT
      DATE_TRUNC(DATE(created_at),day) AS date_,
      EXTRACT(YEAR
      FROM
        created_at) AS year,
      EXTRACT(MONTH
      FROM
        created_at) AS month,
      EXTRACT(DAY
      FROM
        created_at) AS day,
      SAFE_SUBTRACT(retail_price, cost) AS profit,
      products.category AS product_category
    FROM
      `bigquery-public-data.thelook_ecommerce.order_items` orderitems
    INNER JOIN
      `bigquery-public-data.thelook_ecommerce.products` products
    ON
      orderitems.product_id = products.id
      AND created_at >= '2022-06-01 00:00:00 UTC'
      AND created_at <='2022-08-15 23:59:59 UTC'
    GROUP BY
      date_,
      year,
      month,
      day,
      product_category,
      profit )
  SELECT
    a.date_ AS Date,
    a.year,
    a.month,
    a.day,
    a.product_category AS Product_Categories,
    SUM(a.profit) AS Profit
  FROM
    a
  WHERE
    a.day <= 15
  GROUP BY
    a.date_,
    a.year,
    a.month,
    a.day,
    a.product_category
  ORDER BY
    a.date_,
    year,
    month,
    day,
    a.product_category)
SELECT
  Date,
  b.year,
  b.month,
  b.day,
  b.Product_Categories,
  b.profit,
  SUM(Profit) OVER(PARTITION BY product_categories ORDER BY date) AS profit_cumulative
FROM
  b```

CodePudding user response：

I think you are nearly there! You just need to add the month to your WINDOW PARTITION like so:

SUM(Profit) OVER(PARTITION BY product_categories, month ORDER BY date) AS profit_cumulative

CodePudding user response：

I'll try to solve it with this one line of code:

SUM(Profit) OVER(PARTITION BY month, product_categories ORDER BY date) AS profit_cumulative

add the 'month' in your partition by before product categories, so it will reset the cumulative sum every 1st day of the month.