Skip to content
Advertisement

SQL to get 3 adjacent actions without duplicate from the flags

I have a question that a little bit similar with question#66044663 but more complicated.

Here’s my dummy data.

enter image description here

I want to get 3 adjacent actions(no duplicate) from the flag by each user.

Here’s the chart to describe my thought.

enter image description here

Here’s what I want:

enter image description here

How can I implement SQL(I use Google Bigquery)? I know the function LAG could be a solution but I have no idea how to avoid the duplicate actions.

Hope someone can light me up. Thanks a million!

Here’s the code for generating the dataset.

Advertisement

Answer

Consider below

If to apply to sample data in your question – output is

enter image description here

NOTE: above solution works for any number of adjacent actions (no duplicate) – you just need to change it (2 and 3) in two respective places

and

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement