BigQuery / Count the number of rows until a specific row is reached

Question

I have data in BigQuery.

I want to count the number of 'pending' events before their 'approved' event per ID.

How would I get the value for every individual ID?

Table events

id  event
1   pending
1   pending
1   pending
1   approved
2   pending
1   pending
1   pending
1   approved
2   approved

In this example the right result is

id  count_events
1   3
1   2
2   1

Answer 1

Consider below approach

select id, countif(not flag) count_events
from (
  select *, countif(flag) over(partition by id order by ts desc) grp
  from (
    select *, 
      if(lag(event) over(partition by id order by ts) = 'pending' and event = 'approved', true, false) flag
    from your_table
  )
)
group by id, grp 
order by max(ts)    

if applied to sample data in your question - output is

Note use of ts - you must have some column in your table that defines order of events - usually it is timestamp , but can be date, or just sequential number etc.