Sessionization and Funnel Analysis Patterns in SQL
Sessionization groups a stream of timestamped events into discrete sessions by detecting gaps between events that exceed a defined timeout. Funnel analysis measures how many users complete each step in a defined sequence, and how many drop off between steps. Both patterns combine LAG, CTEs, and ordered event data into multi-stage query structures that transform raw event logs into interpretable behavioral summaries.
Sessionization and funnel analysis are the two most common behavioral analytics patterns in SQL. Both transform raw event logs into interpretable summaries using chained CTEs and ordered event data.
Sessionization
A session is a group of events from the same user with no gap longer than a timeout threshold (typically 30 minutes). Sessionization assigns a session ID to each event, grouping consecutive events that belong together.
The approach: use LAG to compute the gap between consecutive events per user, flag which events open a new session, then use a running count of session-start flags to assign session numbers. The gap computation is the first step — you can see it on order data:
Once you can compute the gap, you flag which rows open a new session and assign session numbers with a running count. Here's the full three-step pattern:
WITH event_gaps AS (
SELECT
user_id,
event_time,
event_type,
event_time - LAG(event_time) OVER (
PARTITION BY user_id ORDER BY event_time
) AS gap_to_prev
FROM events
),
session_starts AS (
SELECT
user_id,
event_time,
event_type,
CASE
WHEN gap_to_prev IS NULL
OR gap_to_prev > INTERVAL '30 minutes'
THEN 1 ELSE 0
END AS is_session_start
FROM event_gaps
),
sessions AS (
SELECT
user_id,
event_time,
event_type,
SUM(is_session_start) OVER (
PARTITION BY user_id ORDER BY event_time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_id
FROM session_starts
)
SELECT * FROM sessionsEach CTE does one thing: compute the gap, flag session starts, assign session IDs. The SUM window function acts as a counter — it increments whenever is_session_start = 1 and holds steady otherwise, producing a session number that advances exactly when a new session begins. The first event per user has a NULL gap, which is treated as a session start.
Funnel analysis
A funnel measures how many users completed each step in a defined sequence: signup → onboarding → first purchase. The SQL approach uses one CTE per step, each filtering for users who completed the prior step and finding their earliest qualifying event after the prior step's timestamp.
WITH step_1 AS (
SELECT user_id, MIN(event_time) AS step_1_time
FROM events WHERE event_type = 'signup'
GROUP BY user_id
),
step_2 AS (
SELECT e.user_id, MIN(e.event_time) AS step_2_time
FROM events e
JOIN step_1 s ON s.user_id = e.user_id
WHERE e.event_type = 'onboarding_complete'
AND e.event_time > s.step_1_time
GROUP BY e.user_id
),
step_3 AS (
SELECT e.user_id, MIN(e.event_time) AS step_3_time
FROM events e
JOIN step_2 s ON s.user_id = e.user_id
WHERE e.event_type = 'first_purchase'
AND e.event_time > s.step_2_time
GROUP BY e.user_id
)
SELECT
COUNT(DISTINCT s1.user_id) AS reached_step_1,
COUNT(DISTINCT s2.user_id) AS reached_step_2,
COUNT(DISTINCT s3.user_id) AS reached_step_3
FROM step_1 s1
LEFT JOIN step_2 s2 ON s2.user_id = s1.user_id
LEFT JOIN step_3 s3 ON s3.user_id = s2.user_idEach step CTE takes only users who completed the prior step and applies MIN(event_time) to find their earliest qualifying event in order. The final SELECT LEFT JOINs all steps so users who dropped off still appear in the earlier counts.
The one thing that trips people up
In funnel analysis, the time ordering constraint (e.event_time > s.step_1_time) is what makes it a funnel rather than just a count of who ever did each event. Without it, a user who completed step 2 before step 1 would still be counted — which isn't a funnel conversion.
10 Sessionization and Funnel Analysis Patterns practice problems
Write a query to return each event's `id`, the `session_id` it belongs to, the `event_type`, the `occurred_at` timestamp, and the time elapsed since the previous event in the same session.
Write a query to return each session's `id`, the `user_id` it belongs to, its `started_at` timestamp, and the time elapsed since that user's previous session began.
Write a query to return each event's `id`, the `session_id` it belongs to, its `event_type`, its `occurred_at` timestamp, and the `event_type` of the next event within the same session.
Write a query to return every event that is the first event recorded in its session — those with no preceding event in the same session — returning the event's `id`, `session_id`, `event_type`, and `occurred_at`.
Write a query to return every return-visit session's `user_id`, `session_id`, `started_at`, and `gap_from_prev` — the time elapsed since that user's previous session began.
Write a query to return two counts: the number of users who performed at least one `'page_view'` event (`reached_step_1`), and the number of those users who later performed at least one `'upgrade_clicked'` event after their first `'page_view'` (`reached_step_2`).
Write a query to return each date from January 1, 2022 through January 31, 2022 alongside the count of `'page_view'` events recorded on that date.
Write a query to return three counts: the number of users who performed at least one `'page_view'` (`reached_step_1`), the number who performed `'upgrade_clicked'` strictly after their first `'page_view'` (`reached_step_2`), and the number who performed `'purchase'` strictly after their first qualifying `'upgrade_clicked'` (`reached_step_3`).
Write a query to return each session's `id`, the `user_id` it belongs to, its `started_at`, and its `visit_number` — `1` for the user's earliest session, `2` for their second, and so on.
Write a query to return two metrics: the count of users who performed an `'upgrade_clicked'` event strictly after a prior `'page_view'` (`users_converted`), and the average elapsed time in seconds from each such user's earliest `'page_view'` to their earliest qualifying `'upgrade_clicked'` (`avg_seconds_to_convert`).
These problems are part of the Sessionization and Funnel Analysis Patterns lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.