Scribd
Upload
2 views1 page

Sessionization Guide

The document provides a complete guide on sessionization by grouping events into sessions based on a maximum gap of 30 minutes. It outlines a SQL query process using LAG to identify previous timestamps, calculate time gaps, and create a break flag for gaps exceeding 30 minutes. The final output includes session details such as session start and end times, along with the count of events in each session.

Uploaded by

faizanmba2022
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views1 page

Sessionization Guide

The document provides a complete guide on sessionization by grouping events into sessions based on a maximum gap of 30 minutes. It outlines a SQL query process using LAG to identify previous timestamps, calculate time gaps, and create a break flag for gaps exceeding 30 minutes. The final output includes session details such as session start and end times, along with the count of events in each session.

Uploaded by

faizanmba2022
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Sessionization (Gaps & Islands) – Complete Guide

Problem: Group events into sessions where gap ≤ 30 minutes.

Core Idea:

1. Use LAG to get previous timestamp

2. Calculate time gap

3. Create break flag (gap > 30)

4. Use cumulative SUM to form sessions

WITH cte AS (
SELECT
customer_id,
event_time,
LAG(event_time) OVER (
PARTITION BY customer_id ORDER BY event_time
) AS prev_time
FROM events
),
cte2 AS (
SELECT *,
TIMESTAMPDIFF(MINUTE, prev_time, event_time) AS gap_minutes
FROM cte
),
cte3 AS (
SELECT *,
CASE
WHEN gap_minutes > 30 THEN 1
ELSE 0
END AS break_flag
FROM cte2
),
cte4 AS (
SELECT *,
SUM(break_flag) OVER (
PARTITION BY customer_id ORDER BY event_time
) AS session_id
FROM cte3
)
SELECT
customer_id,
session_id,
MIN(event_time) AS session_start,
MAX(event_time) AS session_end,
COUNT(*) AS events_in_session
FROM cte4
GROUP BY customer_id, session_id;
Key Formula:

LAG → GAP → CASE → SUM → GROUP

You might also like