How can i find rows before a specific value?

Question

I have the next row and what I want to do is to select all the rows before the type "shop". I tried using case in the "where clause" but I didn't get any result. How can I do it?

|id|visitnumber|type    |
|01|          1|register|
|01|          2|visit   |
|01|          3|visit   |
|01|          4|shop    |
|01|          5|visit   |

For example, what I want to get is the visitnumber before type = "shop".

it would be very helpful because what I'm trying to do is to get all the actions that happened before an specific event on big query.

|id|numberofvisits|
|01|             3|

Answer 1

One method uses correlated subqueries:

select id, count(*)
from t
where visitnumber < (select min(t2.visitnumber) from t t2 where t2.id = t.id and type = 'shop')
group by id;

However, in BigQuery, I prefer an approach using window functions:

select id, countif(visitnumber < visitnumber_shop)
from (select t.*,
             min(case when type = 'shop' then visitnumber end) over (partition by id) as visitnumber_shop
      from t
     ) t
group by id;

This has the advantage of keeping all ids even those that don't have a "shop" type.