Next Event Type in Session — Sessionization and Funnels in SQL

The problem

Scenario: Streamhub's UX team is mapping event progression within sessions to understand common user paths.

Task: 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.

Assumptions:

Within a session, events are ordered by occurred_at ascending.
An event's next_event_type is the event_type of the event that immediately follows it within the same session.
The last event in each session has no following event within that session; its next_event_type is reported as a missing value.
Each event's next_event_type is 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, next_event_type.
Sorted by session_id ascending, then occurred_at ascending.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id AS event_id,
  session_id,
  event_type,
  occurred_at,
  LEAD(event_type) OVER (
    PARTITION BY
      session_id
    ORDER BY
      occurred_at
  ) AS next_event_type
FROM
  events
ORDER BY
  session_id,
  occurred_at

The shape

LEAD(event_type) OVER (PARTITION BY session_id ORDER BY occurred_at) looks one row forward inside the same session and pulls that next event's event_type onto the current row. The partition keeps the look-ahead contained to one session, so the last event in a session correctly reports no following event.

Clause by clause

SELECT id AS event_id, session_id, event_type, occurred_at returns the four identifying columns the prompt requests, in order, with the primary key renamed to event_id.
LEAD(event_type) OVER (PARTITION BY session_id ORDER BY occurred_at) AS next_event_type is the mirror of LAG. Where LAG reaches back, LEAD reaches forward by one row inside the same partition. For event 1 in session 1, the next event is a feature_used, so next_event_type is 'feature_used'.
FROM events reads the full event log.
ORDER BY session_id, occurred_at produces the requested sort and matches the window's reading order so each next-event value sits beside the row it belongs to.

The trap

LEAD without PARTITION BY session_id would happily reach into the next session and report its first event as the current session's "next event." There's no error; the value just lies about the user's flow. Partitioning by session_id restarts the window at every session boundary, which is what makes the last event in each session correctly return NULL for next_event_type instead of borrowing from a different session.

You practiced using LEAD partitioned by session_id to attach the next event's type alongside the current event — a forward-looking lookup that resets at each session boundary.

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

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.