Time Since Previous Session — Sessionization and Funnels in SQL

The problem

Scenario: Streamhub's engagement analysts track the time between a user's consecutive sessions to understand re-engagement patterns.

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

Assumptions:

Within a user's history, sessions are ordered by started_at ascending.
A session's time_since_prev_session is the difference between its started_at and the started_at of the user's immediately preceding session.
The first session in each user's history has no preceding session within that user; its time_since_prev_session is reported as a missing value.
Each session's time_since_prev_session is drawn solely from sessions belonging to the same user.

Output:

One row per session.
Columns in this order: session_id, user_id, started_at, time_since_prev_session.
Sorted by user_id ascending, then started_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 session_id,
  user_id,
  started_at,
  started_at - LAG(started_at) OVER (
    PARTITION BY
      user_id
    ORDER BY
      started_at
  ) AS time_since_prev_session
FROM
  sessions
ORDER BY
  user_id,
  started_at

The shape

LAG(started_at) OVER (PARTITION BY user_id ORDER BY started_at) reaches back to the previous session for the same user, and subtracting it from the current row's started_at returns the elapsed time since that user's last visit. The partition is what keeps the look-back inside one user's history.

Clause by clause

SELECT id AS session_id, user_id, started_at returns the three identifying columns in the requested order, renaming id to session_id so the output reads as a session record.
started_at - LAG(started_at) OVER (PARTITION BY user_id ORDER BY started_at) AS time_since_prev_session computes the user-scoped gap. For user 1, session 2 sits 17 days after session 1, so the value is 17 days 04:30:00. The first session per user has no prior row inside the partition, so LAG returns NULL and the subtraction propagates that NULL through.
FROM sessions reads the session table; every session is included because the prompt wants one row per session.
ORDER BY user_id, started_at matches the requested sort and lines up with the window's reading order.

The trap

If PARTITION BY user_id is omitted, LAG looks at the previous row across the entire table, which means user 2's first session would borrow user 1's last session as its prior. The gap value would be wrong but plausible, and nothing would surface the error. Partitioning by user_id restarts the window at the top of each user's history, which is what makes the first session per user correctly report a NULL gap.

You practiced computing per-user session gaps with LAG partitioned by user_id — so each user's lookback stays within their own session history.

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

The shape

Clause by clause

The trap

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