Count number of events before certain events until another event is encountered in the same group in Big query?

Question

I want to count some certain values until a specific event occurred in SQL. This is very similar question to this question:

Count number of events before and after a event “A” till another event “A” is encountered in Big query?

The answer to this question didn't solve my problem which I am confused with RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING too. Differently from that question I don't look for strings but I want to count other events.

My table would be like

   User       Event   Day
    1          C     2019-01-10
    1          B     2019-01-11
    1          D     2019-01-12
    1          A     2019-01-13
    2          D     2019-01-10
    2          B     2019-01-11
    2          C     2019-01-12
    2          D     2019-01-13
    2          A     2019-01-14
    2          E     2019-01-15  

I would like to count C or D until event A or B occured.

I try COUNTIF(Event = 'C' OR Event = 'D') OVER(PARTITION BY User ORDER BY Day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS count_events But this doesn't stop counting until event A or B. It counts all C or D events in partition.

My result table would look like this and the counting would stop if one of the event is occurred and restart counting again when the expected event occurs.

   User       Event   Day           count_events
    1          C     2019-01-10           0
    1          B     2019-01-11           1 
    1          D     2019-01-12           0 
    1          A     2019-01-13           1
    2          D     2019-01-10           0
    2          B     2019-01-11           1 
    2          C     2019-01-12           0
    2          D     2019-01-13           1 
    2          A     2019-01-14           2
    2          E     2019-01-15           0

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(grp), 
  COUNTIF(event IN ('C', 'D')) 
  OVER(PARTITION BY user, grp ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) count_events
FROM (
  SELECT *, 
    COUNTIF(event IN ('A', 'B')) 
      OVER(PARTITION BY user ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) grp
  FROM `project.dataset.table`
)

if to apply to sample data in your question - result is

Row user    event   day         count_events     
1   1       C       2019-01-10  0    
2   1       B       2019-01-11  1    
3   1       D       2019-01-12  0    
4   1       A       2019-01-13  1    
5   2       D       2019-01-10  0    
6   2       B       2019-01-11  1    
7   2       C       2019-01-12  0    
8   2       D       2019-01-13  1    
9   2       A       2019-01-14  2    
10  2       E       2019-01-15  0     

You can test, play with above using below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 user, 'C' event, DATE '2019-01-10' day UNION ALL
  SELECT 1, 'B', '2019-01-11' UNION ALL
  SELECT 1, 'D', '2019-01-12' UNION ALL
  SELECT 1, 'A', '2019-01-13' UNION ALL
  SELECT 2, 'D', '2019-01-10' UNION ALL
  SELECT 2, 'B', '2019-01-11' UNION ALL
  SELECT 2, 'C', '2019-01-12' UNION ALL
  SELECT 2, 'D', '2019-01-13' UNION ALL
  SELECT 2, 'A', '2019-01-14' UNION ALL
  SELECT 2, 'E', '2019-01-15' 
)
SELECT * EXCEPT(grp), 
  COUNTIF(event IN ('C', 'D')) 
  OVER(PARTITION BY user, grp ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) count_events
FROM (
  SELECT *, 
    COUNTIF(event IN ('A', 'B')) 
      OVER(PARTITION BY user ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) grp
  FROM `project.dataset.table`
)
-- ORDER BY user, day

I don't want to count last event C because it didn't occur before event A but after event A

Below is quick "fix"

#standardSQL
SELECT * EXCEPT(grp),   
  COUNTIF(event IN ('A', 'B')) OVER(PARTITION BY user, grp) *
  COUNTIF(event IN ('C', 'D')) 
    OVER(PARTITION BY user, grp ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) count_events
FROM (
  SELECT *, 
    COUNTIF(event IN ('A', 'B')) 
      OVER(PARTITION BY user ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) grp
  FROM `project.dataset.table`
)
-- ORDER BY user, day   

If to apply to recent example you used - result is

Row user    event   day         count_events     
1   1       C       2019-01-10  0    
2   1       B       2019-01-11  1    
3   1       D       2019-01-12  0    
4   1       A       2019-01-13  1    
5   2       D       2019-01-10  0    
6   2       B       2019-01-11  1    
7   2       C       2019-01-12  0    
8   2       D       2019-01-13  1    
9   2       A       2019-01-14  2    
10  2       C       2019-01-15  0    
11  2       E       2019-01-16  0