How to number rows according to values in columns

Imagine I have an event log (ordered by UserID and Start, Start_of_previous_event is added using LAG(), inactive time = Start - Start_of_previous_event):

UserID Event       Start               Start_of_previous_event inactive_time
1      Onboarding  2024-01-01 01:00:00 null                    null
1      Main        2024-01-01 01:01:00 2024-01-01 01:00:00     1
1      Cart        2024-01-01 01:05:00 2024-01-01 01:01:00     4
1      Main        2024-01-01 02:00:00 2024-01-01 01:05:00     55
2      Onboarding  2024-01-01 01:00:00 null                    null

How can I add a column with a session_ids ? New session starts after 30 minutes of inactive time and for new UserID.

Session_id column for the above example:

Is there a way to avoid it if I want to group the resulting table like this:

Select Event, Count(distinct session_id)
from sessions
group by Event

Answer

You can assign the session with date arithmetic and a cumulative sum. Date arithmetic varies by database, but this should give you the idea:

select el.*,
       sum(case when start_of_previous_event > start - interval '30 minute'
                then 0 else 1
           end) over (order by userid order by start) as session_cnt
from eventlog el;

Advertisement

Answer