[hypequery](https://hypequery.com/) › [Recipes](https://hypequery.com/recipes) › Sessionization in ClickHouse with lagInFrame and a typed dataset Index for agents: https://hypequery.com/llms.txt # Sessionization in ClickHouse with lagInFrame and a typed dataset (https://hypequery.com/recipes/sessionization) Split events into 30-minute inactivity sessions per user with lagInFrame and a running sum, then count sessions and average their length with a dataset. ## Run it ```bash npx @hypequery/cli create sessionization -y cd sessionization pnpm install pnpm dev ``` - Packages: @hypequery/datasets, @hypequery/clickhouse (tested from hypequery 2.14.0) - Stack: node, clickhouse - Source: https://github.com/hypequery/hypequery/tree/main/recipes/sessionization - Test: `pnpm test` - Verify: `node dist/recipe.js --json` | Env var | Required | Description | | --- | --- | --- | | CLICKHOUSE_URL | no | ClickHouse HTTP endpoint (default http://localhost:8123) | | CLICKHOUSE_USERNAME | no | ClickHouse user (default: default) | | CLICKHOUSE_PASSWORD | no | ClickHouse password (default: hypequery, the seed's password) | | CLICKHOUSE_DATABASE | no | Database holding the seed tables (default hypequery_examples) | To sessionize in ClickHouse, read each event's previous timestamp with `lagInFrame(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at)`, flag the events whose gap is over 1,800 seconds, and number sessions with a running `sum()` of that flag over the same window; `GROUP BY user_id, session_number` then gives one row per session. On the seed's last week (2026-10-25 to 2026-10-31) that is 26,262 sessions by 1,705 users, 195 seconds and 1.23 events per session on average. The recipe keeps that SQL in a view and puts a hypequery dataset on top for the counts and averages. ## Demo ```text Sessions per day (30-minute timeout), 2026-10-25 to 2026-10-31 day sessions users per user avg length (s) avg events 2026-10-25 1955 798 2.45 84.1 1.10 2026-10-26 4413 1186 3.72 210.1 1.26 2026-10-27 4466 1175 3.80 207.1 1.24 2026-10-28 4415 1177 3.75 203.6 1.24 2026-10-29 4579 1191 3.84 218.1 1.26 2026-10-30 4418 1193 3.70 229.8 1.27 2026-10-31 2016 845 2.39 100.6 1.11 whole week 26262 1705 15.40 195.4 1.23 ``` The 25th and the 31st are a Sunday and a Saturday, when the seed's traffic dips. The seed is sparse per user, so 84.3% of the week's sessions are a single event (length 0); the longest runs 19,194 seconds. ## Install ```bash npx @hypequery/cli create sessionization -y cd sessionization docker compose -f seed/docker-compose.yml up -d --wait && (cd seed && pnpm install && pnpm seed) # or set CLICKHOUSE_* for your own ClickHouse pnpm install pnpm dev ``` `pnpm dev` creates the `sessionization_sessions` view, runs the query, and prints the daily table, the week and the timeout comparison. ## What it shows - **Sessions in two window passes** (`sql/sessions.sql`): `lagInFrame` for the previous event time, then `sum(gap > 1800) OVER (...)` as a running session number, then `GROUP BY` to one row per session with start, end, length and event count. - **Sessions as a dataset** (`src/dataset.ts`): `count` sessions, `countDistinct` users, `avg` length and events, and a derived sessions-per-user, all with `session_start` as the time key so `by: 'day'` works. - **The generated SQL is asserted** (`expected.sql`): hypequery buckets with `toStartOfDay` and parameterises the range. - **Checked against arrays** (`src/smoke.test.ts`): the test rebuilds every session with `groupArray`, `arrayDifference` and `arraySplit`, with no window functions, and the daily counts and averages must match. - **The timeout matters** (`compareTimeouts()` in `src/recipe.ts`): the same week with 15, 30 and 60 minutes, pinned. ## Key file ```ts export const query = { by: 'day', measures: ['sessions', 'users', 'sessionsPerUser', 'avgDurationSeconds', 'avgEventsPerSession'], filters: [between('sessionStart', '2026-10-25 00:00:00', '2026-10-31 23:59:59')], } satisfies DatasetQueryFor; const result = await analytics.execute(Sessions, query); ``` The view at the heart of it: ```sql lagInFrame(occurred_at, 1, toDateTime(0, 'UTC')) OVER w AS previous_at ... sum(toUInt32(dateDiff('second', previous_at, occurred_at) > 1800)) OVER w AS session_number ... WINDOW w AS (PARTITION BY tenant_id, user_id ORDER BY occurred_at, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ``` ## The timeout is the definition | Timeout | Sessions | Average length (s) | | ---: | ---: | ---: | | 15 min | 29,033 | 49.7 | | 30 min | 26,262 | 195.4 | | 60 min | 22,122 | 727.6 | Doubling the timeout from 30 to 60 minutes cuts the session count by 16% and almost quadruples the average length, on the same events. Pick the timeout once, in the view, and every dashboard inherits it. ## lagInFrame details that bite - **The first event.** It has no previous row, so `lagInFrame` returns the default: `1970-01-01 00:00:00` for a `DateTime` (also without the third argument). The gap is then decades and the first event always opens session 1. - **The frame.** `lagInFrame` and `leadInFrame` only see rows inside the window frame, not the whole partition. The default frame ends at the current row, so lag works, but `leadInFrame` under the default frame returns the default value for almost every row (only same-timestamp peers see each other). Spell out the `ROWS` frame, with `UNBOUNDED FOLLOWING` for lead. - **Ties.** Add a unique column to `ORDER BY` (`event_id` here) so same-second events have a stable order. - **Cost.** The view re-sessionizes the whole table on every query: the date filter applies after the window functions, so the query reads all 1,000,000 rows (about 0.4 s on a laptop). Beyond a few hundred million events, materialize sessions into a table on a schedule. ## Architecture ```text events ── VIEW sessionization_sessions lagInFrame → gap > 30 min → running sum = session_number → GROUP BY session │ └─ dataset sessions ── sessions, users, avgDurationSeconds, avgEventsPerSession │ analytics.execute(Sessions, { by: 'day', ... }) ──▶ output.json (asserted in CI) ``` ## Adapt it - **Another timeout.** Change `1800` in the view. The dataset and the query do not change. - **Without window functions.** `arraySplit((t, gap) -> gap > 1800, ts, arrayDifference(...))` over `arraySort(groupArray(occurred_at))` per user gives the same sessions (that is the test's ground truth). It holds each user's events in memory at once, so it suits bounded ranges. - **Per tenant.** Add `dimensions: ['tenantId']`, or set `tenantKey: 'tenant_id'` on the dataset to scope every query to one tenant. - **Sessions with errors.** `error_count` is already in the view; add a measure such as `measure.count('userId', { filters: [gt('errorCount', 0)] })`. - **Materialize it.** Point a refreshable materialized view at the same SELECT and the dataset's `source` at its target table (see the materialized view example). ## Related recipes - [funnel-windowfunnel](https://hypequery.com/recipes/funnel-windowfunnel): a different cut of the same event stream, ordered steps instead of sessions. - [retention-cohorts](https://hypequery.com/recipes/retention-cohorts): the same "shape it in a view, count it in a dataset" pattern. ## Related examples - [materialized-view-rollups](../../examples/materialized-view-rollups): turn the view into a refreshed table. Tested on ClickHouse 25.8 with `@hypequery/datasets` 0.29 and `@hypequery/clickhouse` 2.14. ## Key file: src/recipe.ts ```typescript import fs from 'node:fs'; import path from 'node:path'; import { fileURLToPath } from 'node:url'; import { between, type DatasetQueryFor } from '@hypequery/datasets'; import { analytics, db } from './client.js'; import { Sessions } from './dataset.js'; /** The seed's last full week; fixed so the output is reproducible. */ export const RANGE: [string, string] = ['2026-10-25 00:00:00', '2026-10-31 23:59:59']; /** * The question: per day of the last week, how many sessions started, by how * many users, and how long and how busy was the average session? */ export const query = { by: 'day', measures: ['sessions', 'users', 'sessionsPerUser', 'avgDurationSeconds', 'avgEventsPerSession'], filters: [between('sessionStart', RANGE[0], RANGE[1])], } satisfies DatasetQueryFor; export function toSQL(): string { return analytics.toSQL(Sessions, query); } export async function run() { const result = await analytics.execute(Sessions, query); return result.data; } /** The whole week as one row. */ export async function week() { const { by: _by, ...rest } = query; const result = await analytics.execute(Sessions, rest); return result.data[0]; } export interface TimeoutRow { timeoutMinutes: number; sessions: string; avgDurationSeconds: number; } /** * How much the answer depends on the timeout: the same week sessionized with * 15, 30 and 60 minutes. A raw query, because the timeout lives in the SQL * that builds sessions, not in a measure. */ export async function compareTimeouts(): Promise { const rows: TimeoutRow[] = []; for (const minutes of [15, 30, 60]) { const [row] = await db.rawQuery>( `SELECT count() AS sessions, round(avg(duration), 1) AS avgDurationSeconds FROM ( SELECT user_id, min(occurred_at) AS start, dateDiff('second', min(occurred_at), max(occurred_at)) AS duration FROM ( SELECT user_id, occurred_at, sum(toUInt32(dateDiff('second', previous_at, occurred_at) > ?)) OVER w AS session_number FROM ( SELECT tenant_id, user_id, event_id, occurred_at, lagInFrame(occurred_at, 1, toDateTime(0, 'UTC')) OVER w AS previous_at FROM events WINDOW w AS (PARTITION BY tenant_id, user_id ORDER BY occurred_at, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ) WINDOW w AS (PARTITION BY tenant_id, user_id ORDER BY occurred_at, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ) GROUP BY user_id, session_number ) WHERE start BETWEEN ? AND ?`, [minutes * 60, RANGE[0], RANGE[1]], ); rows.push({ timeoutMinutes: minutes, sessions: row.sessions, avgDurationSeconds: Number(row.avgDurationSeconds) }); } return rows; } const root = path.resolve(path.dirname(fileURLToPath(import.meta.url)), '..'); const isMain = process.argv[1] && import.meta.url === new URL(`file://${process.argv[1]}`).href; if (isMain) { run() .then(async (rows) => { if (process.argv.includes('--write')) { // Capture the artifacts shown on the recipe page and asserted in CI. fs.writeFileSync(path.join(root, 'expected.sql'), `${toSQL()}\n`); fs.writeFileSync(path.join(root, 'output.json'), `${JSON.stringify(rows, null, 2)}\n`); console.log('wrote expected.sql and output.json'); } else if (process.argv.includes('--json')) { console.log(JSON.stringify(rows, null, 2)); } else { console.log(`Sessions per day (30-minute timeout), ${RANGE[0].slice(0, 10)} to ${RANGE[1].slice(0, 10)}\n`); console.table(rows); console.log('\nWhole week:'); console.table([await week()]); console.log('\nTimeout sensitivity:'); console.table(await compareTimeouts()); console.log('\nGenerated SQL:\n' + toSQL()); } await db.close(); }) .catch(async (error) => { console.error(error instanceof Error ? error.message : error); await db.close(); process.exit(1); }); } ``` ## Dataset: src/dataset.ts ```typescript import { dataset, dimension, divide, measure, nullIfZero } from '@hypequery/datasets'; /** * Sessions as a dataset. The view (sql/sessions.sql) turns raw events into * one row per session with a 30-minute inactivity timeout; the dataset counts * and averages those rows, so "sessions per day" or "average session length * per tenant" is a query, not new SQL. */ export const Sessions = dataset('sessions', { source: 'sessionization_sessions', timeKey: 'session_start', dimensions: { tenantId: dimension.number({ column: 'tenant_id', label: 'Tenant' }), userId: dimension.number({ column: 'user_id' }), sessionNumber: dimension.number({ column: 'session_number', label: "Nth session of the user" }), sessionStart: dimension.timestamp({ column: 'session_start', label: 'Session start' }), durationSeconds: dimension.number({ column: 'duration_seconds', label: 'Duration (s)' }), eventCount: dimension.number({ column: 'event_count', label: 'Events in session' }), errorCount: dimension.number({ column: 'error_count' }), }, measures: { sessions: measure.count('userId', { label: 'Sessions' }), users: measure.countDistinct('userId', { label: 'Users with a session' }), avgDurationSeconds: measure.avg('durationSeconds', { label: 'Average session length (s)' }), avgEventsPerSession: measure.avg('eventCount', { label: 'Average events per session' }), events: measure.sum('eventCount', { label: 'Events' }), sessionsPerUser: measure.derived({ label: 'Sessions per user', uses: { sessions: 'sessions', users: 'users' }, formula: ({ sessions, users }) => divide(sessions, nullIfZero(users)), }), }, }); ``` ## Generated SQL ```sql WITH base AS (SELECT toStartOfDay(toDateTime64(sessionization_sessions.session_start, 9, 'UTC')) AS `period`, COUNT(sessionization_sessions.user_id) AS `sessions`, COUNT(DISTINCT sessionization_sessions.user_id) AS `users`, AVG(sessionization_sessions.duration_seconds) AS `avgDurationSeconds`, AVG(sessionization_sessions.event_count) AS `avgEventsPerSession` FROM sessionization_sessions WHERE toDateTime64(sessionization_sessions.session_start, 9, 'UTC') BETWEEN ? AND ? GROUP BY `period`) SELECT `period`, `sessions`, `users`, (toFloat64(`sessions`) / NULLIF(`users`, 0)) AS `sessionsPerUser`, `avgDurationSeconds`, `avgEventsPerSession` FROM base ORDER BY `period` ASC ``` ## Output on the seed dataset ```json [ { "period": "2026-10-25 00:00:00", "sessions": "1955", "users": "798", "sessionsPerUser": "2.4498746867167918", "avgDurationSeconds": "84.14936061381074", "avgEventsPerSession": "1.0971867007672633" }, { "period": "2026-10-26 00:00:00", "sessions": "4413", "users": "1186", "sessionsPerUser": "3.720910623946037", "avgDurationSeconds": "210.13301608882847", "avgEventsPerSession": "1.2583276682528892" }, { "period": "2026-10-27 00:00:00", "sessions": "4466", "users": "1175", "sessionsPerUser": "3.8008510638297874", "avgDurationSeconds": "207.0794894760412", "avgEventsPerSession": "1.2420510523958799" }, { "period": "2026-10-28 00:00:00", "sessions": "4415", "users": "1177", "sessionsPerUser": "3.7510620220900597", "avgDurationSeconds": "203.56534541336353", "avgEventsPerSession": "1.2437146092865232" }, { "period": "2026-10-29 00:00:00", "sessions": "4579", "users": "1191", "sessionsPerUser": "3.8446683459277917", "avgDurationSeconds": "218.0692290893208", "avgEventsPerSession": "1.2550775278445074" }, { "period": "2026-10-30 00:00:00", "sessions": "4418", "users": "1193", "sessionsPerUser": "3.703269069572506", "avgDurationSeconds": "229.77455862381169", "avgEventsPerSession": "1.2700316885468539" }, { "period": "2026-10-31 00:00:00", "sessions": "2016", "users": "845", "sessionsPerUser": "2.3857988165680473", "avgDurationSeconds": "100.59970238095238", "avgEventsPerSession": "1.1106150793650793" } ] ``` ## Related - [Funnel analysis in ClickHouse with windowFunnel and a typed dataset](https://hypequery.com/recipes/funnel-windowfunnel): Count users through login, query and export within 24 hours per tenant with windowFunnel, get conversion per step, and see what each strict mode changes. - [Weekly retention cohorts in ClickHouse with a typed dataset](https://hypequery.com/recipes/retention-cohorts): Turn signup cohorts and weekly activity into a retention dataset: cohort size, active users and retention ratio, sliceable by cohort and week offset. - [ClickHouse materialized view rollups: incremental vs refreshable](https://hypequery.com/examples/materialized-view-rollups): Pre-aggregate a million events two ways, incremental and refreshable, and query both through one typed hypequery dataset. - [All recipes](https://hypequery.com/recipes) - [/clickhouse-product-analytics](https://hypequery.com/clickhouse-product-analytics) - [/clickhouse-time-series](https://hypequery.com/clickhouse-time-series) - [/clickhouse-typescript](https://hypequery.com/clickhouse-typescript)