Query sequences of event with a specific starting event

Question

I'm trying to query event data from firebase. The goal is to get the last event for users with an event sequence starting with event a. The events are ordered by time. I have tried some approaches with lead, join etc. couldn't produce the desired result.

Eample data:

user_id	event_name
1	a
1	b
1	c
2	b
2	a
3	a
4	a
4	b

the ideal output:

user_id	event_name
1	c
3	a
4	b

Answer 1

The events are ordered by time.

So, I assume you do have column named somehow like time
Consider below approach

select  user_id, event_name
from your_table
where true 
qualify 1 = row_number() over(partition by user_id order by time desc)
and 'a' = first_value(event_name) over(partition by user_id order by time)    

if applied to sample data in your question - output is