[hypequery](https://hypequery.com/) › [Recipes](https://hypequery.com/recipes) › Ordered per-user event timelines in ClickHouse with groupArray, arraySort and groupArraySorted
Index for agents: https://hypequery.com/llms.txt

# Ordered per-user event timelines in ClickHouse with groupArray, arraySort and groupArraySorted (https://hypequery.com/recipes/grouparray-user-timelines)

Build each user's event timeline in time order. groupArray's order is not guaranteed; sorting (time, id, value) tuples or groupArraySorted fixes it.

## Run it

```bash
npx @hypequery/cli create grouparray-user-timelines -y
cd grouparray-user-timelines
pnpm install
pnpm dev
```

- Packages: @hypequery/clickhouse, @hypequery/datasets (tested from hypequery 2.14.0)
- Stack: node, clickhouse
- Source: https://github.com/hypequery/hypequery/tree/main/recipes/grouparray-user-timelines
- 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) |

**The short answer:** `groupArray(x)` adds values in whatever order rows reach it, which depends on parts, partitions and threads, not on your timestamp. To get a timeline, collect tuples and sort them: `arrayMap(e -> e.3, arraySort(groupArray((occurred_at, event_id, path))))`. Tuples sort element by element, so that orders by time, then by `event_id` for events in the same second. For only the first N, `groupArraySorted(N)((occurred_at, event_id, path))` keeps the N smallest without collecting the rest.

## Demo

```text
Ordered timelines for users 112, 113, 245 on 2026-10-29

user 112 (5 events): /billing → /billing → /dashboard → /api/v1/query → /api/v1/export
user 113 (5 events): /api/v1/users → /explore → /api/v1/datasets → /export → /dashboard
user 245 (7 events): /api/v1/events → /login → /settings → /reports → /team → /api/v1/events → /billing
```

Each row also carries `gapsSeconds` (seconds since the previous step, `[0, 4329, 5507, ...]` for user 245) and `firstThree` from `groupArraySorted(3)`.

## Install

```bash
npx @hypequery/cli create grouparray-user-timelines -y
cd grouparray-user-timelines
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
```

## What it shows

- **A sorted timeline per user** (`src/recipe.ts`): `arraySort` over `(occurred_at, event_id, path)` tuples, then `arrayMap` to keep the path. `arrayDifference` over the same sorted times gives the gap between steps.
- **`groupArraySorted(3)`** for the first three steps, the bounded alternative to sorting everything.
- **The scalar facts as a dataset** (`src/dataset.ts`): event count, first and last event time, and the first and last path via `argMin` / `argMax`. The test checks they agree with the timeline's first and last element.
- **The pitfalls pinned** (`src/smoke.test.ts`): on the seed, plain `groupArray(occurred_at)` is out of time order for thousands of the 5,033 active users, and the count changes between runs; the sorted version is never out of order.

## Key file

```ts
const SORTED = 'arraySort(groupArray((occurred_at, event_id, path)))';

export function timelines() {
  return db
    .table('events')
    .select([
      'user_id',
      rawAs<string[], 'paths'>(`arrayMap(e -> e.3, ${SORTED})`, 'paths'),
      rawAs<number[], 'gapsSeconds'>(`arrayMap(d -> toInt32(d), arrayDifference(arrayMap(e -> e.1, ${SORTED})))`, 'gapsSeconds'),
      rawAs<string[], 'firstThree'>('arrayMap(e -> e.3, groupArraySorted(3)((occurred_at, event_id, path)))', 'firstThree'),
      rawAs<string, 'events'>('count()', 'events'),
    ])
    .where('user_id', 'in', USERS)
    .where('occurred_at', 'between', DAY)
    .groupBy('user_id')
    .orderBy('user_id', 'ASC');
}
```

## Ways to order a groupArray

| Approach | Ordered? | Notes |
| --- | --- | --- |
| `groupArray(path)` | No | Order is indeterminate. On the seed most users come back out of time order. |
| `groupArray(path)` over a subquery with `ORDER BY` | Not guaranteed | It came back sorted in every run on the seed, but ClickHouse does not promise it; parallel reads or a future optimisation can change it. |
| `arraySort(groupArray((ts, id, path)))` | Yes | Sorts the tuples; `e.3` picks the path back out. |
| `arraySort(x -> x.1, groupArray((ts, id, path)))` | Yes, by `ts` only | Same-second events keep an arbitrary relative order. |
| `groupArraySorted(N)((ts, id, path))` | Yes, ascending | Only the N smallest; memory bounded by N. |
| `arraySlice(arrayReverseSort(groupArray(...)), 1, N)` | Yes, newest first | The latest N; collects everything first. |

## Pitfalls

- **`groupArray(N)` is not "the first N".** `groupArray(3)(x)` stops after any three values. On the seed it differs from `groupArraySorted(3)` for thousands of users.
- **Same-second events.** The seed has 57 (user, second) pairs with two events, across 51 users. Sorting by time alone leaves their order to chance; put a unique column second in the tuple.
- **Parallel arrays.** Two separate `groupArray` calls (`groupArray(ts)`, `groupArray(path)`) then `arraySort((p, t) -> t, paths, times)` relies on the two arrays being built in step. One array of tuples cannot drift.
- **Memory.** `groupArray` holds every value for every group in memory. A timeline per user over a year is fine at seed scale; at billions of rows filter the time range first, cap with `groupArraySorted(N)`, or use `groupArray(max_size)` when any N values will do.
- **Int64 in JSON.** `arrayDifference` returns `Array(Int64)`, which the HTTP JSON formats quote as strings by default. The recipe narrows to `Int32` to get numbers.
- **The builder and raw aggregates.** When you add a helper aggregation such as `.count()`, hypequery infers `GROUP BY` from selections it does not recognise as aggregates, and `arrayMap(... groupArray ...)` is one of those. Keep the aggregate raw (`rawAs('count()', 'events')`) and call `.groupBy()` yourself, as the key file does.

## Architecture

```text
events ──WHERE user, day──▶ groupArray((occurred_at, event_id, path))   one tuple per event, any order
                                    │ arraySort                          time order, event_id breaks ties
                                    ├─▶ arrayMap(e -> e.3)              paths
                                    └─▶ arrayDifference(e.1)            gapsSeconds
src/recipe.ts   timelines().toSQL()   ──▶ expected.sql  (asserted in CI)
                timelines().execute() ──▶ output.json   (asserted exactly in CI)
src/dataset.ts  argMin/argMax(path, occurred_at) ──▶ must equal paths[0] and paths[-1]
```

## Adapt it

- **Sessions.** Split a timeline where the gap exceeds 30 minutes: `arraySplit((e, g) -> g > 1800, sorted, gaps)` gives one array per session.
- **Funnels.** For "did the user go /login then /explore then /export", `windowFunnel` or `sequenceMatch` answer it directly without building the array.
- **Paths as strings.** `arrayStringConcat(paths, ' > ')` for a readable journey column, or `count()` grouped by it to find the most common journeys.
- **Several fields.** Keep the whole tuple (`arraySort(groupArray((occurred_at, event_id, event_type, path)))`) and pick fields with `e.3`, `e.4`.

## Related recipes

- [top-n-per-group-limit-by](https://hypequery.com/recipes/top-n-per-group-limit-by): top N rows per group with `LIMIT BY`.
- [retention-cohorts](https://hypequery.com/recipes/retention-cohorts): per-user activity rolled up into cohorts.

## Related examples

- [nextjs-dashboard](https://hypequery.com/examples/nextjs-dashboard): where a user journey view would live.

Tested on ClickHouse 25.8 with `@hypequery/clickhouse` 2.14 and `@hypequery/datasets` 0.29.

## Key file: src/recipe.ts

```typescript
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { rawAs } from '@hypequery/clickhouse';
import { between, inList, type DatasetQueryFor } from '@hypequery/datasets';
import { analytics, db } from './client.js';
import { UserActivity } from './dataset.js';

/** One day and three users, fixed so the output is reproducible. */
export const DAY: [string, string] = ['2026-10-29 00:00:00', '2026-10-29 23:59:59'];
export const USERS = [112, 113, 245];

/**
 * groupArray keeps values in whatever order the rows reach it, which depends
 * on parts, partitions and threads, not on time. Collect (time, id, value)
 * tuples instead and sort them: tuples compare element by element, so this
 * orders by time, then event_id for events in the same second.
 */
const SORTED = 'arraySort(groupArray((occurred_at, event_id, path)))';

export function timelines() {
  return db
    .table('events')
    .select([
      'user_id',
      rawAs<string[], 'paths'>(`arrayMap(e -> e.3, ${SORTED})`, 'paths'),
      // Seconds since the previous step; arrayDifference returns Int64 (a JSON
      // string by default), so narrow it to Int32 to get numbers back.
      rawAs<number[], 'gapsSeconds'>(`arrayMap(d -> toInt32(d), arrayDifference(arrayMap(e -> e.1, ${SORTED})))`, 'gapsSeconds'),
      // groupArraySorted(N) keeps the N smallest values, sorted, without collecting the rest.
      rawAs<string[], 'firstThree'>('arrayMap(e -> e.3, groupArraySorted(3)((occurred_at, event_id, path)))', 'firstThree'),
      // Raw, like the arrays: the builder infers GROUP BY keys from selections
      // it does not recognise as aggregates when a helper such as .count() is
      // added, and arrayMap(... groupArray ...) is one of those.
      rawAs<string, 'events'>('count()', 'events'),
    ])
    .where('user_id', 'in', USERS)
    .where('occurred_at', 'between', DAY)
    .groupBy('user_id')
    .orderBy('user_id', 'ASC');
}

/** The same users and day through the dataset: scalar first/last facts. */
export const summaryQuery = {
  dimensions: ['userId'],
  measures: ['events', 'firstEventAt', 'lastEventAt', 'firstPath', 'lastPath'],
  filters: [inList('userId', USERS), between('occurredAt', DAY[0], DAY[1])],
  orderBy: [{ field: 'userId', direction: 'asc' }],
} satisfies DatasetQueryFor<typeof UserActivity>;

export function toSQL(): string {
  return timelines().toSQL();
}

export async function run() {
  return timelines().execute();
}

export async function runSummary() {
  return (await analytics.execute(UserActivity, summaryQuery)).data;
}

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(`Ordered timelines for users ${USERS.join(', ')} on ${DAY[0].slice(0, 10)}\n`);
        for (const row of rows) console.log(`user ${row.user_id} (${row.events} events): ${row.paths.join(' → ')}`);
        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, measure } from '@hypequery/datasets';

/**
 * Per-user activity summary: how many events, when the first and last
 * happened, and which path each was on. These are scalar aggregates, so they
 * are dataset measures every caller can share.
 *
 * The ordered timeline itself is an array (`groupArray`), which is not a
 * dataset measure type today; src/recipe.ts builds it with the query builder,
 * and the smoke test checks the two agree on the first and last step.
 */
export const UserActivity = dataset('userActivity', {
  source: 'events',
  timeKey: 'occurred_at',
  dimensions: {
    eventId: dimension.number({ column: 'event_id' }),
    userId: dimension.number({ column: 'user_id' }),
    occurredAt: dimension.timestamp({ column: 'occurred_at' }),
    path: dimension.string(),
  },
  measures: {
    events: measure.count('eventId', { label: 'Events' }),
    firstEventAt: measure.min('occurredAt', { label: 'First event' }),
    lastEventAt: measure.max('occurredAt', { label: 'Last event' }),
    firstPath: measure.argMin('path', 'occurredAt', { label: 'First path' }),
    lastPath: measure.argMax('path', 'occurredAt', { label: 'Last path' }),
  },
});
```

## Generated SQL

```sql
SELECT user_id, arrayMap(e -> e.3, arraySort(groupArray((occurred_at, event_id, path)))) AS paths, arrayMap(d -> toInt32(d), arrayDifference(arrayMap(e -> e.1, arraySort(groupArray((occurred_at, event_id, path)))))) AS gapsSeconds, arrayMap(e -> e.3, groupArraySorted(3)((occurred_at, event_id, path))) AS firstThree, count() AS events FROM events WHERE user_id IN (112, 113, 245) AND occurred_at BETWEEN '2026-10-29 00:00:00' AND '2026-10-29 23:59:59' GROUP BY user_id ORDER BY user_id ASC
```

## Output on the seed dataset

```json
[
  {
    "user_id": 112,
    "paths": [
      "/billing",
      "/billing",
      "/dashboard",
      "/api/v1/query",
      "/api/v1/export"
    ],
    "gapsSeconds": [
      0,
      25235,
      6849,
      8382,
      21385
    ],
    "firstThree": [
      "/billing",
      "/billing",
      "/dashboard"
    ],
    "events": "5"
  },
  {
    "user_id": 113,
    "paths": [
      "/api/v1/users",
      "/explore",
      "/api/v1/datasets",
      "/export",
      "/dashboard"
    ],
    "gapsSeconds": [
      0,
      32308,
      14164,
      16274,
      3312
    ],
    "firstThree": [
      "/api/v1/users",
      "/explore",
      "/api/v1/datasets"
    ],
    "events": "5"
  },
  {
    "user_id": 245,
    "paths": [
      "/api/v1/events",
      "/login",
      "/settings",
      "/reports",
      "/team",
      "/api/v1/events",
      "/billing"
    ],
    "gapsSeconds": [
      0,
      4329,
      5507,
      6677,
      2201,
      3856,
      378
    ],
    "firstThree": [
      "/api/v1/events",
      "/login",
      "/settings"
    ],
    "events": "7"
  }
]
```

## Related

- [Top N per group in ClickHouse with LIMIT BY, compared with row_number() and rank()](https://hypequery.com/recipes/top-n-per-group-limit-by): Get the top 3 paths for every tenant in one query with LIMIT 3 BY, typed in TypeScript, and see how ties change the answer under rank() and dense_rank().
- [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.
- [All recipes](https://hypequery.com/recipes)
- [/clickhouse-product-analytics](https://hypequery.com/clickhouse-product-analytics)
- [/clickhouse-query-builder](https://hypequery.com/clickhouse-query-builder)
- [/clickhouse-typescript](https://hypequery.com/clickhouse-typescript)