Bigquery navigation function calculate id

June 25, 2023

I’m new to bigquery and I’m having trouble using bigquery’s navigation functions.
I don’t understand how these functions work.

I have a table that has this structure:

Event_id	created_ts
1	2020-01-01 12:00:00
2	2020-01-01 12:01:00
3	2020-01-01 12:05:00
4	2020-01-01 12:06:00

And I would like to calculate the session id which changes after more than one minute of inactivity.

Session_id	created_ts
1	2020-01-01 12:00:00
1	2020-01-01 12:01:00
2	2020-01-01 12:05:00
2	2020-01-01 12:06:00

Here is the SQL code I started writing:

select event_id, 
    created_ts, 
    EXTRACT(minute FROM created_ts) as minute,
    date_diff(date(created_ts),coalesce(date(lag(created_ts - minute) over(partition by event_id order by event_id)), date(created_ts)),
) session_id
from table1

>Solution :

You can first compare the minutes of the created datetime and see which is bigger than 1, and then filter oiz all unwanted rows

WITH CTE AS (
select event_id, 
    created_ts, 
CASE WHEN TIMESTAMP_DIFF( coalesce(date(lag(created_ts - minute) over(partition by event_id order by event_id)), date(created_ts)),date(created_ts), MINUTE) > 1 then 1 ELSE 0 END bg
from table1)
SELECT event_id, 
    created_ts
FROM CTE WHERE bg = 1