Calculate Time Between Logins by User in Bigquery

I am trying to calculate the time difference between 2 login events in BigQuery, by user. I want to understand how long it takes between logins for each user.

I’ve tried with the following query:

Select user_id, timestamp,
    timestamp_diff(timestamp, (lag(timestamp)over (partition by user_id order by timestamp), minute)) as 
    time_diff
from table 
order by user_id, timestamp

Sample Data

user_id	event	timestamp
aaa	Login	2021-02-20 00:00:00 UTC
bbb	Login	2021-02-20 00:02:00 UTC
aaa	Login	2021-02-20 00:01:00 UTC
ccc	Login	2021-02-20 00:02:00 UTC
aaa	Login	2021-02-20 00:01:00 UTC
aaa	Login	2021-02-20 00:50:00 UTC
bbb	Login	2021-02-20 00:13:00 UTC
ccc	Login	2021-02-20 00:46:00 UTC

My result should be a column wth the time between the current and the previous login event, the first event would be NULL.

Answer

You have extra parentheses in your query. So, written correctly:

Select user_id, timestamp,
       timestamp_diff(timestamp, 
                      lag(timestamp) over (partition by user_id order by timestamp),
                      minute
                     ) as time_diff
from table 
order by user_id, timestamp;

You might also want to add the filter where event = 'Login'.