How to get the rows (based on timestamp) of the table that reached the last intent

I’m trying to find in some chatbot logs the users that reached the last intent, but I need the intent before that one.

i.e. Table 1

ID	Intent
1	last
1	first
2	first
3	second
4	last
4	second
4	first

would need to get this

ID	Intent
1	first
4	second

Answer

Consider below approach (note you need some column that define order of intents – in below example I assume ts as a timestamp column)

select id, prev_intent as intent
from (
    select *, lag(intent) over(partition by id order by ts) prev_intent
    from `project.dataset.table`    
)
where intent = 'last'    

if applied to sample data in your question – output is