Session summary per user

The growth team wants a per-user engagement summary from the raw events table. Two problems sit in the raw data:

1. Duplicate ingests. Some events were stored twice. A copy has the same user_id, event_type and event_ts as the original. Count each event once.
2. No session id. A session is a run of a user’s events with no gap of more than 30 minutes between one event and the next. A gap of exactly 30 minutes stays in the same session.

Return one row per user with:

column	type	meaning
user_id	integer	the user
sessions	integer	number of sessions
events	integer	unique events across all sessions
avg_events_per_session	decimal	events / sessions, rounded to 2 decimals
longest_session_minutes	decimal	longest time from a session’s first event to its last, in minutes, rounded to 1 decimal

A session with one event lasts 0 minutes. Timestamps can carry seconds, so measure gaps and lengths as real durations, not by counting clock minutes.

Row order doesn’t matter.

Tables: events.

Approach

Deduplicate, sessionise, aggregate to sessions, then aggregate to users.

WITH unique_events AS (
  SELECT DISTINCT user_id, event_type, event_ts
  FROM events
),
flags AS (
  SELECT user_id,
         event_ts,
         CASE
           WHEN event_ts - LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts)
                <= INTERVAL 30 MINUTE THEN 0
           ELSE 1
         END AS is_new_session
  FROM unique_events
),
numbered AS (
  SELECT user_id,
         event_ts,
         SUM(is_new_session) OVER (
           PARTITION BY user_id
           ORDER BY event_ts
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS session_no
  FROM flags
),
sessions AS (
  SELECT user_id,
         session_no,
         COUNT(*)                                AS events,
         epoch(MAX(event_ts) - MIN(event_ts)) / 60 AS minutes
  FROM numbered
  GROUP BY user_id, session_no
)
SELECT user_id,
       COUNT(*)                         AS sessions,
       SUM(events)                      AS events,
       ROUND(SUM(events) / COUNT(*), 2) AS avg_events_per_session,
       ROUND(MAX(minutes), 1)           AS longest_session_minutes
FROM sessions
GROUP BY user_id;

Why it works

Order matters in the pipeline. Deduplication comes first because every later count depends on it. Sessionisation comes before any aggregation because a session is defined by neighbouring rows, which only exist before you group.

The CASE handles the first event without a separate IS NULL branch. With no previous event, LAG returns NULL, the subtraction is NULL, the comparison is NULL, and CASE falls through to ELSE 1. That’s correct, but it’s quiet: write the IS NULL check out if your team prefers code that reads the same as the rule.

Two rows with the same user and timestamp but different event types are two events. They survive the DISTINCT, sit zero minutes apart, and land in the same session.

epoch() turns the interval into seconds as a number. Dividing by 60 gives minutes, and the output stays a plain number rather than an interval.

Under the hood

The DISTINCT is a hash aggregate on three columns. The two windows share a partition and order, so DuckDB sorts each user’s events once. Then come two hash aggregates: one keyed by (user_id, session_no), one by user_id. The sort dominates the cost. Everything after it is a single pass.