Part 175: Sessionization Analysis in SQL: Window Lag & Inactivity Thresholds

Grouping continuous clickstream events into discrete user sessions using pure SQL.

1. The Sessionization Problem

In web analytics, a user's events (pageviews, clicks) form a "session". A session expires if the user is inactive for more than 30 minutes. How do we assign a distinct session_id to each group of events without procedural code?

2. The 3-Step SQL Solution

WITH event_gaps AS (
    -- Step 1: Calculate idle time since previous event for each user
    SELECT 
        user_id,
        event_time,
        page_url,
        LAG(event_time) OVER (
            PARTITION BY user_id 
            ORDER BY event_time
        ) AS prev_event_time
    FROM user_clicks
),
session_flags AS (
    -- Step 2: Flag 1 if gap > 30 minutes (or if first event), else 0
    SELECT 
        user_id,
        event_time,
        page_url,
        CASE 
            WHEN prev_event_time IS NULL THEN 1
            WHEN EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 > 30 THEN 1
            ELSE 0
        END AS is_new_session
    FROM event_gaps
),
session_ids AS (
    -- Step 3: Cumulative sum over flags creates unique session increment!
    SELECT 
        user_id,
        event_time,
        page_url,
        CONCAT(user_id, '_s', SUM(is_new_session) OVER (
            PARTITION BY user_id 
            ORDER BY event_time
        )) AS session_id
    FROM session_flags
)
SELECT * FROM session_ids;

Key Takeaways

• The cumulative sum over a boolean flag (SUM(is_new_session) OVER (...)) is the standard SQL pattern for interval clustering.

In Part 176, we enter Batch 7 with Funnel Conversion Analysis in Pure SQL!