Rolling Three Session Average — Window Frames in SQL

The problem

Streamhub's product team tracks user engagement momentum by watching how session activity trends across each user's recent history.

Write a query to return every session's ID, user ID, event count, and the average event_count across that user's current session plus the two immediately preceding sessions chronologically.

Assumptions:

Within each user's sessions, the rolling-3 average at each row covers that session plus the two sessions with the largest started_at strictly before it. The window is restricted to that user.
For a user's first session, the rolling-3 average equals that session's event_count. For a user's second session, the average covers two sessions. From the third session onward, it covers three.
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 rolling_3_avg. 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,
  AVG(event_count) OVER (
    PARTITION BY
      user_id
    ORDER BY
      started_at ROWS BETWEEN 2 PRECEDING
      AND CURRENT ROW
  ) AS rolling_3_avg
FROM
  sessions
ORDER BY
  user_id,
  started_at

The shape

AVG(event_count) OVER (PARTITION BY user_id ORDER BY started_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) is the rolling-3 trailing average, scoped per user. Each row gets the mean of the current session plus the two sessions immediately before it in that user's timeline.

Clause by clause

SELECT id, user_id, event_count, AVG(event_count) OVER (PARTITION BY user_id ORDER BY started_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3_avg returns the session identifiers and the windowed average. PARTITION BY user_id keeps each user's history separate so one user's activity never leaks into another's average; ORDER BY started_at defines the chronological sequence; ROWS BETWEEN 2 PRECEDING AND CURRENT ROW pins the frame to three physical row positions.
FROM sessions reads every session.
ORDER BY user_id, started_at sorts the result so the trend reads top to bottom inside each user's block.

The trap

The rolling average reflects whatever rows are actually in the frame. On a user's first session it equals that one session's count; on the second it averages two. A reader expecting "always three sessions averaged" can mistake the early-row values for a calculation error. The frame is doing exactly what ROWS BETWEEN 2 PRECEDING AND CURRENT ROW says: include the current row plus up to two prior rows, no more, no less.

You practiced AVG(...) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) — fixed-position rolling average trailing across the most recent records in each partition.

Return every session's ID, user ID, event count, and the average `event_count` across that user's current session plus the two immediately preceding sessions chronologically

The shape

Clause by clause

The trap

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