First Session Event Count — FIRST_VALUE and LAST

The problem

Streamhub's product team wants every session compared against that user's very first session.

Write a query to return every session's ID, user ID, event count, and the event count from that same user's first session chronologically.

Assumptions:

The sessions table has one row per session with an id, a user_id, an event_count, and a started_at timestamp.
A user's first session is the session with the smallest started_at for that user_id. The same first-session event count appears on every row sharing a user_id.
The final result is sorted by user_id ascending, then by started_at ascending.

Output:

One row per session, with columns id, user_id, event_count, and first_session_events. Sorted by user_id, then started_at.

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,
  user_id,
  event_count,
  FIRST_VALUE(event_count) OVER (
    PARTITION BY
      user_id
    ORDER BY
      started_at
  ) AS first_session_events
FROM
  sessions
ORDER BY
  user_id,
  started_at

The shape

The "anchor from history" pattern: every record carries the partition's earliest value alongside its own. FIRST_VALUE(event_count) looks up the value at position 1 of each user's chronologically-ordered sessions and attaches that single number to every row in the user's window. The analyst can now read each session's event count and the user's first-session event count side by side, on the same row.

Clause by clause

SELECT id, user_id, event_count, FIRST_VALUE(event_count) OVER (PARTITION BY user_id ORDER BY started_at) AS first_session_events returns each session's identifying columns plus the user's first-session event count. PARTITION BY user_id carves the table into one window per user; ORDER BY started_at defines the chronological order inside that window; FIRST_VALUE pulls back the event count at position 1.
FROM sessions reads every session.
ORDER BY user_id, started_at sorts the printed result chronologically within each user so the partition reads top to bottom in time order.

The trap

The default frame of a window function with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That default does not bite FIRST_VALUE, because position 1 of the partition is in every running frame from the very first row onward. FIRST_VALUE returns the same value on every row whether the frame is explicit or default. LAST_VALUE and NTH_VALUE behave differently and need an explicit full-partition frame; FIRST_VALUE does not.

You practiced FIRST_VALUE for an anchor-from-history pattern — every record carries the partition's earliest value alongside its own.

Return every session's ID, user ID, event count, and the event count from that same user's first session chronologically

The shape

Clause by clause

The trap

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