Web analytics counts sessions (visits), but the data is a stream of events. The common rule: a new session starts with a visitor's first event, or after more than 30 minutes without one.
Three steps, each a window function:
- Flag new sessions:
LAG(event_time) per visitor, and is_new = 1 when there's no previous event or the gap is over 30 minutes.
- Number them: a running
SUM(is_new) per visitor in time order gives 1, 1, 1, 2, 2, ...
- Summarize per (visitor, session number): start, end, events, whether it reached a purchase.
SUM(is_new) OVER (PARTITION BY visitor_id ORDER BY event_time, event_id) AS session_no
Add a tie-breaker (event_id) to the order, because two events can share a timestamp. The 30-minute rule is a convention; tools differ, so state the rule you used next to the numbers.