For each row, return the minutes elapsed since that user's PREVIOUS event (NULL for the user's first event), as exact fractional minutes — a 90-second gap is 1.5, not 1. The first building block for any sessionization pipeline.
Sort by user_id then event_ts ascending.
Returnevent_iduser_idevent_tsmins_since_prev
About this problem
This problem practises the first step of sessionization: measuring the gap between each event and the same user’s previous event. From sess_events, you use LAG per user_id, ordered by event_ts with event_id as a tie-breaker, and express the gap as exact fractional minutes, so ninety seconds is 1.5. Each user’s first event has no predecessor and shows NULL. Every later step – flagging session starts, numbering sessions, measuring duration – reads this column, so it has to be right for every row.
The precision trap is the one most people miss. Many date functions that “return the difference in minutes” actually count how many minute boundaries were crossed, or return whole numbers, so a ninety-second gap comes back as 1 or 2 instead of 1.5. Most sample events fall on whole minutes, so this looks perfect until u_c’s ninety-second gap appears. Dividing by the integer 60 can truncate as well. The other trap is the window itself. A LAG window ordered only by time runs across all users, so it reaches into the previous user’s events. In the sample, u_b’s events happen before u_a’s, so a global window gives u_a’s first event a gap instead of NULL, and session boundaries later merge two people’s journeys. When two events share a timestamp, ordering by time alone also leaves their order, and so the gap, to chance.
Read the lesson: Sessionization & Event Sequences (P24) →