Sessionization and Funnel Analysis Patterns in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
What are Sessionization and Funnels in SQL?
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.
How do you group events into sessions in SQL?
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:
SELECT customer_id, ordered_at::date AS order_date, total_amount, ordered_at - LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS gap_to_prev_order FROM orders ORDER BY customer_id, ordered_at LIMIT 15
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.
How do you build a funnel query in SQL?
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.
What turns a step count into a real funnel?
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.
Practice Sessionization and Funnels in SQL
Scenario: Streamhub's product team is tracking how long users spend between actions within a session.
Task: 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.
Assumptions:
- Within a session, events are ordered by
occurred_atascending. - An event's
time_since_previs the difference between itsoccurred_atand theoccurred_atof the immediately preceding event in the same session. - The first event in each session has no preceding event within that session; its
time_since_previs reported as a missing value. - Each event's
time_since_previs drawn solely from events within the same session.
Output:
- One row per recorded event.
- Columns in this order:
event_id,session_id,event_type,occurred_at,time_since_prev. - Sorted by
session_idascending, thenoccurred_atascending.
Schema · analytics5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Sessionization and Funnels 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).
Start learning to practice all 10 Sessionization and Funnels problems, with instant grading and mastery tracking.
Common questions about Sessionization and Funnels
What timeout should define a session?
Thirty minutes of inactivity is the common convention, and it is the default in Google Analytics. The right gap depends on your own data: longer than the normal pauses inside a single visit, and shorter than the usual gap between separate visits.
Why use LAG to find sessions?
LAG puts the previous event’s timestamp on the current row, so the gap between two events becomes a simple subtraction. Any gap longer than the timeout marks the first event of a new session.
How do I turn session starts into session numbers?
Flag each event that starts a new session with 1 and every other event with 0, then take a running SUM of that flag. Every event in the same session ends up carrying the same number.
Should sessions be calculated separately for each user?
Yes. Put PARTITION BY user_id inside both the LAG and the running SUM. Without it, one user’s last event is compared with the next user’s first, and sessions leak across people.
