Clickstream Sessionization with Inactivity Gaps

WITH lagged AS
  (SELECT user_id,
          event_timestamp,
          CASE
              WHEN event_timestamp - lag(event_timestamp) OVER (PARTITION BY user_id
                                                                ORDER BY event_timestamp) > INTERVAL 30 MINUTE
                   OR lag(event_timestamp) OVER (PARTITION BY user_id
                                                 ORDER BY event_timestamp) IS NULL THEN 1
              ELSE 0
          END AS is_new_session
   FROM user_events),
     session_indexed AS
  (SELECT user_id,
          event_timestamp,
          cast(sum(is_new_session) OVER (PARTITION BY user_id
                                         ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BIGINT) AS session_id
   FROM lagged)
SELECT user_id,
       session_id,
       min(event_timestamp) AS session_start,
       max(event_timestamp) AS session_end,
       count(*) AS event_count
FROM session_indexed
GROUP BY user_id,
         session_id
ORDER BY user_id ASC,
         session_start ASC;

CREATE TABLE user_events (event_id INT, user_id INT, event_timestamp TIMESTAMP, page_url VARCHAR);


INSERT INTO user_events
VALUES (1, 10, '2025-01-01 10:00:00', '/home'),
       (2, 10, '2025-01-01 10:15:00', '/pricing'),
       (3, 10, '2025-01-01 11:00:00', '/docs'),
       (4, 10, '2025-01-01 11:10:00', '/checkout'),
       (5, 20, '2025-01-01 09:00:00', '/blog');