[hypequery](https://hypequery.com/) › [Recipes](https://hypequery.com/recipes) › p95 latency per event type in ClickHouse with a typed dataset
Index for agents: https://hypequery.com/llms.txt

# p95 latency per event type in ClickHouse with a typed dataset (https://hypequery.com/recipes/p95-latency)

Define p50, p95 and p99 latency once as dataset measures and get the quantile() SQL, the result on a million events, and the exactness trade-off.

## Run it

```bash
npx @hypequery/cli create p95-latency -y
cd p95-latency
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/p95-latency
- 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) |

## Demo

```text
p95 latency per event type, 2026-10-25 to 2026-10-31

eventType   events   p50   p95     p99
export        1233  1128  2609    3451
query_run     4851   514  1080    1405
error          984   244   513     681
login         2644   210   445     603
api_call      8073   152   324     431
page_view    14549   102   219     290
```

## Install

```bash
npx @hypequery/cli create p95-latency -y
cd p95-latency
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

- **Percentiles as measures** (`src/dataset.ts`): `measure.median`, `measure.percentile(field, 0.95)` and `measure.percentile(field, 0.99)` next to a plain `count`, named once for every caller.
- **One semantic query, one SQL statement** (`src/recipe.ts`): group by event type, order by p95; hypequery renders `quantile(0.95)(events.latency_ms)` and parameterises the date range.
- **The generated SQL is asserted** (`expected.sql`): the smoke test fails if a package upgrade changes it.
- **The exactness trade-off** (`src/smoke.test.ts`): `quantile` is approximate; the test checks it lands within 3% of `quantileExact` over the same rows, and documents when to swap.

## Key file

```ts
export const query: DatasetQuery = {
  dimensions: ['eventType'],
  measures: ['events', 'p50LatencyMs', 'p95LatencyMs', 'p99LatencyMs'],
  filters: [between('occurredAt', RANGE[0], RANGE[1])],
  orderBy: [{ field: 'p95LatencyMs', direction: 'desc' }],
};

const result = await analytics.execute(Events, query);
```

## Architecture

```text
src/dataset.ts   dataset('events') with percentile measures
      │
src/recipe.ts    analytics.toSQL(Events, query)  ──▶  expected.sql   (asserted in CI)
                 analytics.execute(Events, query) ──▶  output.json    (asserted in CI, ±2%)
```

## Adapt it

- **Exact when it matters.** `quantile` samples up to 8,192 values per group. For SLO reporting on small groups, or when two teams must agree to the millisecond, use `quantileExact` in SQL; in a dataset today that means a plain `measure.max`/`min` plus a `rawQuery` for the exact figure, or a rollup table that stores `quantilesState`.
- **Per tenant.** Add `tenantId` to `dimensions` in the query, or set `tenantKey: 'tenant_id'` on the dataset to scope every call to one tenant.
- **Over time.** Replace `dimensions: ['eventType']` with `by: 'day'` to get a daily p95 series; the measures do not change.
- **Pre-aggregate.** For dashboards over billions of rows, store `quantileTDigestState(latency_ms)` in an AggregatingMergeTree target (see the materialized view example) and read it with a `-Merge` query.

## Related recipes

- [retention-cohorts](https://hypequery.com/recipes/retention-cohorts): the same dataset pattern over a cohort view.

## Related examples

- [materialized-view-rollups](https://hypequery.com/examples/materialized-view-rollups): pre-aggregate the same events.
- [nextjs-dashboard](https://hypequery.com/examples/nextjs-dashboard): the p95 tile on a dashboard.

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 { Events } 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: for each event type over the last week, how many requests
 * were there and what were the p50, p95 and p99 latencies? Slowest first.
 */
export const query = {
  dimensions: ['eventType'],
  measures: ['events', 'p50LatencyMs', 'p95LatencyMs', 'p99LatencyMs'],
  filters: [between('occurredAt', RANGE[0], RANGE[1])],
  orderBy: [{ field: 'p95LatencyMs', direction: 'desc' }],
} satisfies DatasetQueryFor<typeof Events>;

export function toSQL(): string {
  return analytics.toSQL(Events, query);
}

export async function run() {
  const result = await analytics.execute(Events, query);
  return result.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(`p95 latency per event type, ${RANGE[0].slice(0, 10)} to ${RANGE[1].slice(0, 10)}\n`);
        console.table(rows);
        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';

/**
 * Request events with latency. `measure.percentile` renders ClickHouse's
 * `quantile(level)`, an approximate percentile that is fast and uses bounded
 * memory on any number of rows. Swap in `quantileExact` only when the exact
 * value matters more than the cost.
 */
export const Events = dataset('events', {
  source: 'events',
  timeKey: 'occurred_at',
  dimensions: {
    eventId: dimension.number({ column: 'event_id' }),
    occurredAt: dimension.timestamp({ column: 'occurred_at' }),
    eventType: dimension.string({ column: 'event_type', label: 'Event type' }),
    path: dimension.string(),
    tenantId: dimension.number({ column: 'tenant_id' }),
    latencyMs: dimension.number({ column: 'latency_ms' }),
  },
  measures: {
    events: measure.count('eventId', { label: 'Requests' }),
    p50LatencyMs: measure.median('latencyMs', { label: 'p50 latency (ms)' }),
    p95LatencyMs: measure.percentile('latencyMs', 0.95, { label: 'p95 latency (ms)' }),
    p99LatencyMs: measure.percentile('latencyMs', 0.99, { label: 'p99 latency (ms)' }),
    maxLatencyMs: measure.max('latencyMs', { label: 'Max latency (ms)' }),
  },
});
```

## Generated SQL

```sql
SELECT events.event_type AS `eventType`, COUNT(events.event_id) AS `events`, quantile(0.5)(events.latency_ms) AS `p50LatencyMs`, quantile(0.95)(events.latency_ms) AS `p95LatencyMs`, quantile(0.99)(events.latency_ms) AS `p99LatencyMs` FROM events WHERE toDateTime64(events.occurred_at, 9, 'UTC') BETWEEN ? AND ? GROUP BY `eventType` ORDER BY `p95LatencyMs` DESC
```

## Output on the seed dataset

```json
[
  {
    "eventType": "export",
    "events": "1233",
    "p50LatencyMs": "1128",
    "p95LatencyMs": "2609.3999999999996",
    "p99LatencyMs": "3451.280000000003"
  },
  {
    "eventType": "query_run",
    "events": "4851",
    "p50LatencyMs": "514",
    "p95LatencyMs": "1080",
    "p99LatencyMs": "1405"
  },
  {
    "eventType": "error",
    "events": "984",
    "p50LatencyMs": "244",
    "p95LatencyMs": "513",
    "p99LatencyMs": "681"
  },
  {
    "eventType": "login",
    "events": "2644",
    "p50LatencyMs": "210",
    "p95LatencyMs": "444.8499999999999",
    "p99LatencyMs": "602.7100000000005"
  },
  {
    "eventType": "api_call",
    "events": "8073",
    "p50LatencyMs": "152",
    "p95LatencyMs": "324.39999999999964",
    "p99LatencyMs": "431"
  },
  {
    "eventType": "page_view",
    "events": "14549",
    "p50LatencyMs": "102",
    "p95LatencyMs": "219",
    "p99LatencyMs": "290"
  }
]
```

## Related

- [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.
- [Next.js dashboard on ClickHouse with cross-filtering](https://hypequery.com/examples/nextjs-dashboard): A Next.js 16 dashboard over ClickHouse: one typed dataset, a serve endpoint, React hooks, and click-to-filter across every panel.
- [All recipes](https://hypequery.com/recipes)
- [/clickhouse-typescript](https://hypequery.com/clickhouse-typescript)
- [/clickhouse-time-series](https://hypequery.com/clickhouse-time-series)
- [/clickhouse-real-time-analytics](https://hypequery.com/clickhouse-real-time-analytics)