Scribd
Upload
2 views1 page

Sessionization Guide

The document outlines a method for sessionizing events by grouping them into sessions based on a maximum gap of 30 minutes between events. It details the SQL process using Common Table Expressions (CTEs) to calculate time gaps, create break flags, and assign session IDs. The final output includes customer IDs, session IDs, session start and end times, and 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 outlines a method for sessionizing events by grouping them into sessions based on a maximum gap of 30 minutes between events. It details the SQL process using Common Table Expressions (CTEs) to calculate time gaps, create break flags, and assign session IDs. The final output includes customer IDs, session IDs, session start and end times, and 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