Time Since Previous Event — Sessionization and Funnels in SQL

The problem

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_at ascending.
An event's time_since_prev is the difference between its occurred_at and the occurred_at of the immediately preceding event in the same session.
The first event in each session has no preceding event within that session; its time_since_prev is reported as a missing value.
Each event's time_since_prev 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, time_since_prev.
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,
  occurred_at - LAG(occurred_at) OVER (
    PARTITION BY
      session_id
    ORDER BY
      occurred_at
  ) AS time_since_prev
FROM
  events
ORDER BY
  session_id,
  occurred_at

The shape

LAG(occurred_at) OVER (PARTITION BY session_id ORDER BY occurred_at) retrieves the previous event's timestamp inside the same session, and subtracting it from the current row's occurred_at produces the elapsed time since the prior action. The partition keeps the window contained to one session at a time, so the gap never crosses a session boundary.

Clause by clause

SELECT id AS event_id, session_id, event_type, occurred_at returns the four event columns the prompt asks for in order, with id renamed to event_id to match the requested output name.
occurred_at - LAG(occurred_at) OVER (PARTITION BY session_id ORDER BY occurred_at) AS time_since_prev computes the gap. LAG looks at the event one position earlier inside the same session_id, ordered by occurred_at. The subtraction returns an interval like 00:06:00 for the second event in session 1 and 00:07:00 for the third.
FROM events reads the full event log; every event is in scope because the prompt wants one row per recorded event.
ORDER BY session_id, occurred_at sorts the result the way the prompt asks, which also happens to match the window's reading order so the gaps land beside the right rows.

The trap

LAG with no PARTITION BY would happily reach across sessions and compute the time between, say, the last event of session 1 and the first event of session 2. The result still looks like a number, so nothing breaks visibly. The fix is the partition: PARTITION BY session_id makes LAG restart at the top of each session, which is what gives the first event in each session a NULL gap instead of borrowing from the previous session's last event.

You practiced computing per-event gaps with LAG partitioned by session_id — so the lookback resets at each session boundary instead of crossing into a different session's history.

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

The shape

Clause by clause

The trap

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