Clickstream Sessionization with Window Functions

What you will learn

• Calculate time gaps between consecutive events using LAG() partitioned by user
• Apply inactivity threshold logic using conditional CASE WHEN expressions
• Generate monotonic session sequence identifiers using cumulative window sums
• Aggregate session duration and pageview depth for digital marketing analytics

What you need first

SELECT event_id, event_time, LAG(event_time) OVER (ORDER BY event_time) AS prev_time FROM web_events LIMIT 2;

event_id | event_time          | prev_time
---------+---------------------+--------------------
1        | 2024-03-01 10:00:00 | NULL
2        | 2024-03-01 10:15:00 | 2024-03-01 10:00:00

SELECT 1 AS flag, SUM(1) OVER (ORDER BY 1 ROWS UNBOUNDED PRECEDING) AS running_counter;

flag | running_counter
-----+----------------
1    | 1

Architecture and data flow

The data

Raw clickstream event stream recording user pageviews with precise timestamps.

CREATE OR REPLACE TABLE web_events AS SELECT * FROM (VALUES
  (1, 101, TIMESTAMP '2024-03-01 10:00:00', '/home'),
  (2, 101, TIMESTAMP '2024-03-01 10:15:00', '/search'),
  (3, 101, TIMESTAMP '2024-03-01 10:50:00', '/product/12'),
  (4, 102, TIMESTAMP '2024-03-01 11:00:00', '/home'),
  (5, 102, TIMESTAMP '2024-03-01 11:20:00', '/checkout'),
  (6, 101, TIMESTAMP '2024-03-01 11:55:00', '/cart')
) AS t(event_id, user_id, event_time, page_url);

Step by step

Read each step, run its code, then check your output against the one shown.

1. 01

   Inspect raw clickstream event sequence

   We begin by querying the raw web_events table ordered by user identifier and event timestamp. Observing user 101 shows 4 total events: 10:00:00, 10:15:00, 10:50:00, and 11:55:00. The time gap between 10:15 and 10:50 is 35 minutes, while the gap between 10:50 and 11:55 is 65 minutes. Both intervals exceed the 30-minute threshold, meaning user 101 engaged in 3 separate browsing sessions. By sorting events chronologically within each user partition, we expose the discrete user journey and verify that raw telemetry timestamps accurately capture natural pauses between distinct browsing sessions.

   SQLStep 1Copy

   SELECT event_id, user_id, event_time, page_url FROM web_events ORDER BY user_id, event_time;

   Show outputGuess what it prints first

   event_id | user_id | event_time          | page_url
   ---------+---------+---------------------+------------
   1        | 101     | 2024-03-01 10:00:00 | /home
   2        | 101     | 2024-03-01 10:15:00 | /search
   3        | 101     | 2024-03-01 10:50:00 | /product/12
   6        | 101     | 2024-03-01 11:55:00 | /cart
   4        | 102     | 2024-03-01 11:00:00 | /home
   5        | 102     | 2024-03-01 11:20:00 | /checkout

   Common mistake

   Sorting by event_id instead of event_time, which causes incorrect event sequencing if IDs arrive out of order from asynchronous collectors.

   How you will know

   You will see 6 clickstream events sorted sequentially by user ID and event timestamp.

2. 02

   Attempt naive hourly truncation grouping

   Wrong turn first: this step shows the mistake before the fix

   A common naive shortcut is grouping events by fixed calendar hours using DATE_TRUNC('hour', event_time). Running this query groups events 1 and 2 into the 10:00 hour bucket, but splits event 3 if it occurred near an hour boundary. More critically, two separate visits separated by 45 minutes that happen to fall within the same clock hour are merged, while a continuous 10-minute session crossing 10:55 to 11:05 is split. Wall-clock boundaries impose rigid artificial limits that fail to capture human browsing behavior, distorting digital marketing attribution and inflating bounce rate measurements across web properties.

   SQLStep 2Copy

   SELECT user_id, DATE_TRUNC('hour', event_time) AS session_hour, COUNT(*) AS event_count FROM web_events GROUP BY user_id, DATE_TRUNC('hour', event_time) ORDER BY user_id, session_hour;

   Show outputGuess what it prints first

   user_id | session_hour        | event_count
   --------+---------------------+------------
   101     | 2024-03-01 10:00:00 | 3
   101     | 2024-03-01 11:00:00 | 1
   102     | 2024-03-01 11:00:00 | 2

   Common mistake

   Using wall-clock hour truncation instead of elapsed session inactivity thresholds.

   How you will know

   Observe that clock truncation arbitrarily splits or merges sessions without regard to actual user inactivity.

3. 03

   Compute elapsed inactivity minutes with LAG

   We employ the LAG() window function partitioned by user_id and ordered by event_time to inspect the timestamp of the immediately preceding pageview. We compute the difference in seconds using DuckDB's EPOCH function and divide by 60.0 to obtain elapsed inactivity minutes. For the first event of any user, LAG returns NULL, indicating the start of a user's initial interaction. Partitioning strictly by user identifier ensures that timestamp lags never bleed across distinct user journeys.

   SQLStep 3Copy

   SELECT
     user_id,
     event_id,
     event_time,
     page_url,
     LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time,
     ROUND(EPOCH(event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)) / 60.0, 1) AS diff_minutes
   FROM web_events
   ORDER BY user_id, event_time;

   Show outputGuess what it prints first

   user_id | event_id | event_time          | page_url    | prev_event_time     | diff_minutes
   --------+----------+---------------------+-------------+---------------------+-------------
   101     | 1        | 2024-03-01 10:00:00 | /home       | NULL                | NULL
   101     | 2        | 2024-03-01 10:15:00 | /search     | 2024-03-01 10:00:00 | 15
   101     | 3        | 2024-03-01 10:50:00 | /product/12 | 2024-03-01 10:15:00 | 35
   101     | 6        | 2024-03-01 11:55:00 | /cart       | 2024-03-01 10:50:00 | 65
   102     | 4        | 2024-03-01 11:00:00 | /home       | NULL                | NULL
   102     | 5        | 2024-03-01 11:20:00 | /checkout   | 2024-03-01 11:00:00 | 20

   Common mistake

   Forgetting to partition by user_id, causing user 102's first event to calculate its time difference from user 101's last event.

   How you will know

   The output shows diff_minutes as NULL for the first event of each user, 15.0 for event 2, and 35.0 for event 3.

4. 04

   Flag session boundary transitions

   Next, we classify whether each pageview represents the start of a new session. Using a CASE WHEN statement, an event is marked as a new session (flag = 1) if prev_time IS NULL (the user's first recorded event) or if the elapsed inactivity exceeds 30.0 minutes. Otherwise, the event belongs to the ongoing session (flag = 0). This binary flag serves as the mathematical foundation for our cumulative session numbering. Explicitly converting temporal threshold conditions into discrete binary indicators simplifies downstream analytical transformations.

   SQLStep 4Copy

   WITH lag_calc AS (
     SELECT
       *,
       LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
     FROM web_events
   )
   SELECT
     user_id,
     event_id,
     event_time,
     page_url,
     CASE
       WHEN prev_time IS NULL OR EPOCH(event_time - prev_time) / 60.0 > 30.0 THEN 1
       ELSE 0
     END AS is_new_session
   FROM lag_calc
   ORDER BY user_id, event_time;

   Show outputGuess what it prints first

   user_id | event_id | event_time          | page_url    | is_new_session
   --------+----------+---------------------+-------------+---------------
   101     | 1        | 2024-03-01 10:00:00 | /home       | 1
   101     | 2        | 2024-03-01 10:15:00 | /search     | 0
   101     | 3        | 2024-03-01 10:50:00 | /product/12 | 1
   101     | 6        | 2024-03-01 11:55:00 | /cart       | 1
   102     | 4        | 2024-03-01 11:00:00 | /home       | 1
   102     | 5        | 2024-03-01 11:20:00 | /checkout   | 0

   Common mistake

   Using >= 30.0 without handling NULL previous timestamps, which drops the very first event from being flagged as session 1.

   How you will know

   Binary flag is_new_session is 1 for events 1, 3, 4, and 6, and 0 for events 2 and 5.

5. 05

   Synthesize monotonic session IDs and duration summary

   Finally, we apply a cumulative SUM(is_new_session) window function partitioned by user_id and ordered by event_time to assign monotonic session identifiers (1, 2, 3...). We then group by user_id and session_num to compute key session metrics: start timestamp, end timestamp, pageview count, and total session duration in minutes. The resulting analytical table is ready for marketing attribution and funnel analysis. By computing cumulative session identifiers over ordered partitions, this pattern creates robust session boundaries that scale efficiently to billions of clickstream records without requiring stateful external session caching services.

   SQLStep 5Copy

   WITH lag_calc AS (
     SELECT
       *,
       LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
     FROM web_events
   ),
   flagged AS (
     SELECT
       *,
       CASE
         WHEN prev_time IS NULL OR EPOCH(event_time - prev_time) / 60.0 > 30.0 THEN 1
         ELSE 0
       END AS is_new_session
     FROM lag_calc
   ),
   sessions AS (
     SELECT
       user_id,
       event_id,
       event_time,
       CAST(SUM(is_new_session) OVER (
         PARTITION BY user_id
         ORDER BY event_time
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS INT) AS session_num
     FROM flagged
   )
   SELECT
     user_id,
     session_num,
     MIN(event_time) AS session_start,
     MAX(event_time) AS session_end,
     COUNT(*) AS page_views,
     ROUND(EPOCH(MAX(event_time) - MIN(event_time)) / 60.0, 1) AS duration_minutes
   FROM sessions
   GROUP BY user_id, session_num
   ORDER BY user_id, session_num;

   Show outputGuess what it prints first

   user_id | session_num | session_start       | session_end         | page_views | duration_minutes
   --------+-------------+---------------------+---------------------+------------+-----------------
   101     | 1           | 2024-03-01 10:00:00 | 2024-03-01 10:15:00 | 2          | 15
   101     | 2           | 2024-03-01 10:50:00 | 2024-03-01 10:50:00 | 1          | 0
   101     | 3           | 2024-03-01 11:55:00 | 2024-03-01 11:55:00 | 1          | 0
   102     | 1           | 2024-03-01 11:00:00 | 2024-03-01 11:20:00 | 2          | 20

   Common mistake

   Omitting ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which in some engines defaults to range frames that mishandle duplicate timestamps.

   How you will know

   Output summarizes 4 discrete sessions across users 101 and 102 with accurate start, end, and pageview counts.

Build it yourself

Write your solution below, run it in your browser, and pass every check.

What the result means

Interpretation

The sessionization query reveals that user 101 engaged in 3 separate browsing sessions rather than one extended visit. Their first session lasted 15 minutes across home and search pages, followed by a 35-minute gap before viewing a product, and an additional 65-minute gap before returning to cart. Segmenting by inactivity boundaries prevents marketing models from crediting early browsing ad campaigns with late-funnel checkout events.

The message you would send

Hi Growth Team, the SQL clickstream sessionization logic is complete. By establishing a strict 30-minute inactivity cutoff, we eliminated infinite sessions and accurately segmented user 101 into 3 distinct visits. Retargeting attribution models can now reliably isolate conversion credit to the specific session where purchase intent occurred.

Put it on your CV

Resume or LinkedIn bullet

Implemented enterprise web event sessionization in SQL utilizing LAG() window functions, EPOCH time math, and cumulative window aggregations in DuckDB.

README for GitHub

# Clickstream Sessionization with Window Functions

A high-performance SQL pipeline converting raw clickstream event logs into bounded user sessions based on a 30-minute inactivity threshold.

## Features
- Inactivity boundary detection via LAG() window function
- Monotonic session ID generation using cumulative SUM()
- Session start, end, pageview count, and duration aggregation
- Ready for digital marketing attribution and clickstream funnel models

Keep going

• LessonSQL window functions
• Case studyThe Duplicate Click Problem
• Case studyWhere Do Shoppers Drop Off?