Scribd
Upload
3 views2 pages

Sessionization Practice

The document contains SQL queries for sessionization practices, addressing various aspects such as assigning session IDs based on time gaps, calculating session start and end times, and determining total sessions and average durations per user. It also includes queries for identifying bounce sessions, longest sessions, first and last events per session, sessions per day, and gaps between sessions. Each query is structured with common table expressions (CTEs) to facilitate analysis of user event data.

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
3 views2 pages

Sessionization Practice

The document contains SQL queries for sessionization practices, addressing various aspects such as assigning session IDs based on time gaps, calculating session start and end times, and determining total sessions and average durations per user. It also includes queries for identifying bounce sessions, longest sessions, first and last events per session, sessions per day, and gaps between sessions. Each query is structured with common table expressions (CTEs) to facilitate analysis of user event data.

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 SQL Practice – Questions & Answers

Q1: Assign session_id (gap > 30 minutes)


WITH cte AS (
SELECT user_id, event_time,
LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event
FROM user_events
),
cte2 AS (
SELECT user_id, event_time,
CASE
WHEN prev_event IS NULL THEN 1
WHEN TIMESTAMPDIFF(MINUTE, prev_event, event_time) > 30 THEN 1
ELSE 0
END AS break_flag
FROM cte
)
SELECT user_id, event_time,
SUM(break_flag) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM cte2;

Q2: Session start, end, duration


WITH sessionized AS (
-- same logic as Q1
)
SELECT user_id, session_id,
MIN(event_time) AS session_start,
MAX(event_time) AS session_end,
TIMESTAMPDIFF(MINUTE, MIN(event_time), MAX(event_time)) AS session_duration
FROM sessionized
GROUP BY user_id, session_id;

Q3: Total sessions & avg duration per user


WITH sessions AS (
SELECT user_id, session_id,
TIMESTAMPDIFF(MINUTE, MIN(event_time), MAX(event_time)) AS session_duration
FROM sessionized
GROUP BY user_id, session_id
)
SELECT user_id,
COUNT(*) AS total_sessions,
AVG(session_duration) AS avg_session_duration
FROM sessions
GROUP BY user_id;

Q4: Bounce sessions (only 1 event)


SELECT user_id, session_id, event_time
FROM (
SELECT *,
COUNT(*) OVER (PARTITION BY user_id, session_id) AS event_count
FROM sessionized
) t
WHERE event_count = 1;

Q5: Longest session per user


WITH sessions AS (
SELECT user_id, session_id,
TIMESTAMPDIFF(MINUTE, MIN(event_time), MAX(event_time)) AS session_duration
FROM sessionized
GROUP BY user_id, session_id
)
SELECT user_id, session_id, session_duration
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_duration DESC) AS rn
FROM sessions
) t
WHERE rn = 1;

Q6: First event per session


SELECT user_id, session_id, MIN(event_time) AS first_event
FROM sessionized
GROUP BY user_id, session_id;

Q7: Last event per session


SELECT user_id, session_id, MAX(event_time) AS last_event
FROM sessionized
GROUP BY user_id, session_id;

Q8: Sessions per day


WITH sessions AS (
SELECT user_id, session_id,
MIN(event_time) AS session_start
FROM sessionized
GROUP BY user_id, session_id
)
SELECT DATE(session_start) AS session_date,
COUNT(*) AS total_sessions
FROM sessions
GROUP BY DATE(session_start);

Q9: Gap between sessions


SELECT user_id, session_id,
LAG(session_end) OVER (PARTITION BY user_id ORDER BY session_start) AS prev_session_end,
session_start,
TIMESTAMPDIFF(MINUTE,
LAG(session_end) OVER (PARTITION BY user_id ORDER BY session_start),
session_start) AS gap_minutes
FROM sessions;

You might also like