Running total with several conditions in bigquery

I need to calculate a running total but need to reset the total on a condition (when expected reached = 0 and product_group and product changes). Got help here without the two extra fields: Calculate a running total with a condition in BigQuery I have this table and may use product_group and product as integers or strings as below.

Date, Product_group, Product, Registrations, Expected Registrations, Expected Reached, Running Total
            2020-03-01,A, Bikes, 5, 4,1, 1
            2020-03-02,A, Bikes, 7, 5,1, 2
            2020-03-03,A, Bikes, 8, 6,1, 3
            2020-03-04,A, Bikes, 2, 5,0, 0
            2020-03-05,A, Bikes, 5, 4,1, 1
            2020-03-06,A, Bikes, 7, 5,1, 2 
            2020-03-04,B, Cars , 2, 5,0, 0
            2020-03-05,B, Cars , 5, 4,1, 1
            2020-03-06,B, Cars , 7, 5,1, 2
            2020-03-07,B, Cars , 8, 6,1, 3 
            2020-03-08,C, Plane, 2, 5,0, 0

Any suggestions how to adapt this query (answer from the other post) that works fine without the two extra fields-

#standardSQL
SELECT * EXCEPT(grp), 
  SUM(Expected_reached) OVER(PARTITION BY grp ORDER BY `date`) Running_Total
FROM (
  SELECT *, COUNTIF(Expected_reached = 0) OVER(ORDER BY `date`) grp 
  FROM `project.dataset.table`
)

The problem is that COUNTIF(Expected_reached = 0) OVER(ORDER BYdate) grp starts over when the product_group or product changes and I get non unique groups so the running total SUM(Expected_reached) OVER(PARTITION BY grp ORDER BYdate) Running_Total doesnt calculate correctly.

Answer

You just simply need to add PARTITION BY Product_group, Product to both analytic functions

#standardSQL
SELECT * EXCEPT(grp), 
  SUM(Expected_reached) OVER(PARTITION BY Product_group, Product, grp ORDER BY `date`) Running_Total
FROM (
  SELECT *, COUNTIF(Expected_reached = 0) OVER(PARTITION BY Product_group, Product ORDER BY `date`) grp 
  FROM `project.dataset.table`
)

Advertisement

Answer