I'm trying to query event data from firebase. The goal is to get the event sequence that starts with event X and ending with event Y. The events are ordered by date.
Eample data:
| user_id | event_name | date |
|---|---|---|
| 1 | X | |
| 1 | b | |
| 1 | c | |
| 1 | Y | |
| 2 | a | |
| 2 | Y | |
| 3 | X | |
| 3 | a | |
| 3 | b | |
| 4 | X | |
| 4 | b | |
| 4 | Y |
the ideal output:
| user_id | event_name | date |
|---|---|---|
| 1 | X | |
| 1 | b | |
| 1 | c | |
| 1 | Y | |
| 4 | X | |
| 4 | b | |
| 4 | Y |
Consider below approach
select * except(group_id) from (
select *,
countif(event_name = 'X') over(partition by user_id order by date rows between unbounded preceding and current row) -
countif(event_name = 'Y') over(partition by user_id order by date rows between unbounded preceding and 1 preceding) group_id,
from your_table
)
qualify 2 = countif(event_name in ('X', 'Y')) over(partition by user_id, group_id)
if applied to sample data in your question - output is
