[hypequery](https://hypequery.com/) › [Recipes](https://hypequery.com/recipes) › Weekly retention cohorts in ClickHouse with a typed dataset
Index for agents: https://hypequery.com/llms.txt

# 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.

## Run it

```bash
npx @hypequery/cli create retention-cohorts -y
cd retention-cohorts
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/retention-cohorts
- 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
Weekly retention, signup cohorts 2026-06-01 to 2026-06-30

cohort      size   w0    w1    w2    w3    w4    w5    w6
2026-06-01    93  55%   72%   59%   59%   58%   55%   55%
2026-06-08    88  69%   64%   59%   58%   56%   55%   51%
2026-06-15    91  67%   69%   68%   63%   62%   62%   56%
2026-06-22    72  63%   71%   56%   54%   50%   50%   50%
2026-06-29    94  65%   70%   61%   60%   62%   60%   52%
```

## Install

```bash
npx @hypequery/cli create retention-cohorts -y
cd retention-cohorts
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 `user_cohort_activity` view, runs the query and prints the grid.

## What it shows

- **Shape the data once, in SQL** (`sql/cohort-activity.sql`): a view joins users to events, tags each active week with the user's signup cohort, and carries the cohort size via a window function.
- **Retention as a dataset** (`src/dataset.ts`): `countDistinct(userId)` for active users, `max(cohortSize)` to read the window value back, and a derived `retention = active / size`.
- **One query, the familiar grid** (`src/recipe.ts`): group by cohort week and weeks-since-signup, then a ten-line pivot for display.
- **Exactness** (`src/smoke.test.ts`): distinct counts are exact, so the captured `output.json` must match exactly, and a hand-written SQL query is the ground truth.

## Key file

```ts
export const query: DatasetQuery = {
  dimensions: ['cohortWeek', 'weeksSinceSignup'],
  measures: ['cohortSize', 'activeUsers', 'retention'],
  filters: [between('cohortWeek', '2026-06-01 00:00:00', '2026-06-30 23:59:59'), lte('weeksSinceSignup', 6)],
  orderBy: [{ field: 'cohortWeek', direction: 'asc' }, { field: 'weeksSinceSignup', direction: 'asc' }],
};
```

## Architecture

```text
users ──┐
        ├─ VIEW user_cohort_activity (cohort_week, cohort_size, user_id, weeks_since_signup)
events ─┘        │
                 └─ dataset CohortRetention ── activeUsers / cohortSize = retention
                          │
                 analytics.execute(CohortRetention, query) ──▶ 35 rows ──▶ toGrid() ──▶ 5 cohorts × 7 weeks
```

## Adapt it

- **Daily or monthly cohorts.** Change `toStartOfWeek` to `toDate` or `toStartOfMonth` in the view, and the `dateDiff` unit to match. The dataset and the query do not change.
- **Retention relative to first activity.** Replace `signed_up_at` in the view's cohort CTE with each user's first event (`min(occurred_at) OVER (PARTITION BY user_id)`).
- **Per tenant.** Add `tenant_id` to the view and `tenantKey: 'tenant_id'` to the dataset; every query is then scoped to one tenant.
- **Materialize it.** On large tables, turn the view into a table filled by a refreshable materialized view (see the refreshable guide) and point `source` at it.
- **Serve it.** Register the dataset with `@hypequery/serve` and the same query becomes a `useDataset('cohortRetention', …)` hook in React.

## Related recipes

- [p95-latency](https://hypequery.com/recipes/p95-latency): the same dataset pattern over raw events.

## Related examples

- [nextjs-dashboard](https://hypequery.com/examples/nextjs-dashboard): where a retention grid would live.
- [materialized-view-rollups](https://hypequery.com/examples/materialized-view-rollups): make the view a refreshable 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, lte, type DatasetQueryFor } from '@hypequery/datasets';
import { analytics, db } from './client.js';
import { CohortRetention } from './dataset.js';

/** June 2026 signup cohorts, followed for six weeks. Fixed so the output is reproducible. */
export const COHORTS: [string, string] = ['2026-06-01', '2026-06-30'];
export const MAX_WEEKS = 6;

/**
 * The question: of the users who signed up each week, what share was active
 * 0, 1, 2 ... 6 weeks later? One row per (cohort, week offset).
 */
export const query = {
  dimensions: ['cohortWeek', 'weeksSinceSignup'],
  measures: ['cohortSize', 'activeUsers', 'retention'],
  filters: [between('cohortWeek', `${COHORTS[0]} 00:00:00`, `${COHORTS[1]} 23:59:59`), lte('weeksSinceSignup', MAX_WEEKS)],
  orderBy: [
    { field: 'cohortWeek', direction: 'asc' },
    { field: 'weeksSinceSignup', direction: 'asc' },
  ],
} satisfies DatasetQueryFor<typeof CohortRetention>;

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

export async function run() {
  const result = await analytics.execute(CohortRetention, query);
  return result.data;
}

/** Pivot rows into the familiar cohort grid: one line per cohort, one column per week. */
export function toGrid(rows: Awaited<ReturnType<typeof run>>): Array<Record<string, string>> {
  const grid = new Map<string, Record<string, string>>();
  for (const row of rows) {
    const cohort = String(row.cohortWeek).slice(0, 10);
    const line = grid.get(cohort) ?? { cohort, size: String(row.cohortSize) };
    line[`w${row.weeksSinceSignup}`] = `${(Number(row.retention) * 100).toFixed(0)}%`;
    grid.set(cohort, line);
  }
  return [...grid.values()];
}

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')) {
        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(`Weekly retention, signup cohorts ${COHORTS[0]} to ${COHORTS[1]}\n`);
        console.table(toGrid(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, divide, measure, nullIfZero } from '@hypequery/datasets';

/**
 * Retention as a dataset. The view (sql/cohort-activity.sql) yields one row
 * per user per active week with the cohort it belongs to; the dataset turns
 * that into cohort size, active users and a retention ratio that callers can
 * slice by cohort and by weeks since signup without rewriting the SQL.
 */
export const CohortRetention = dataset('cohortRetention', {
  source: 'user_cohort_activity',
  timeKey: 'cohort_week',
  timeGrains: ['week', 'month', 'quarter'],
  dimensions: {
    cohortWeek: dimension.timestamp({ column: 'cohort_week', label: 'Signup week' }),
    weeksSinceSignup: dimension.number({ column: 'weeks_since_signup', label: 'Weeks since signup' }),
    userId: dimension.number({ column: 'user_id' }),
    cohortSizeColumn: dimension.number({ column: 'cohort_size' }),
  },
  measures: {
    activeUsers: measure.countDistinct('userId', { label: 'Active users' }),
    // Every row of a cohort carries the same cohort_size, so max() reads it back.
    cohortSize: measure.max('cohortSizeColumn', { label: 'Cohort size' }),
    retention: measure.derived({
      label: 'Retention',
      description: 'Share of the signup cohort active in the week',
      uses: { active: 'activeUsers', size: 'cohortSize' },
      formula: ({ active, size }) => divide(active, nullIfZero(size)),
    }),
  },
});
```

## Generated SQL

```sql
WITH base AS (SELECT user_cohort_activity.cohort_week AS `cohortWeek`, user_cohort_activity.weeks_since_signup AS `weeksSinceSignup`, MAX(user_cohort_activity.cohort_size) AS `cohortSize`, COUNT(DISTINCT user_cohort_activity.user_id) AS `activeUsers` FROM user_cohort_activity WHERE toDateTime64(user_cohort_activity.cohort_week, 9, 'UTC') BETWEEN ? AND ? AND user_cohort_activity.weeks_since_signup <= ? GROUP BY `cohortWeek`, `weeksSinceSignup`) SELECT `cohortWeek`, `weeksSinceSignup`, `cohortSize`, `activeUsers`, (toFloat64(`activeUsers`) / NULLIF(`cohortSize`, 0)) AS `retention` FROM base ORDER BY `cohortWeek` ASC, `weeksSinceSignup` ASC
```

## Output on the seed dataset

```json
[
  {
    "cohortWeek": "2026-06-01",
    "weeksSinceSignup": "0",
    "cohortSize": "93",
    "activeUsers": "51",
    "retention": "0.5483870967741935"
  },
  {
    "cohortWeek": "2026-06-01",
    "weeksSinceSignup": "1",
    "cohortSize": "93",
    "activeUsers": "67",
    "retention": "0.7204301075268817"
  },
  {
    "cohortWeek": "2026-06-01",
    "weeksSinceSignup": "2",
    "cohortSize": "93",
    "activeUsers": "55",
    "retention": "0.5913978494623656"
  },
  {
    "cohortWeek": "2026-06-01",
    "weeksSinceSignup": "3",
    "cohortSize": "93",
    "activeUsers": "55",
    "retention": "0.5913978494623656"
  },
  {
    "cohortWeek": "2026-06-01",
    "weeksSinceSignup": "4",
    "cohortSize": "93",
    "activeUsers": "54",
    "retention": "0.5806451612903226"
  },
  {
    "cohortWeek": "2026-06-01",
    "weeksSinceSignup": "5",
    "cohortSize": "93",
    "activeUsers": "51",
    "retention": "0.5483870967741935"
  },
  {
    "cohortWeek": "2026-06-01",
    "weeksSinceSignup": "6",
    "cohortSize": "93",
    "activeUsers": "51",
    "retention": "0.5483870967741935"
  },
  {
    "cohortWeek": "2026-06-08",
    "weeksSinceSignup": "0",
    "cohortSize": "88",
    "activeUsers": "61",
    "retention": "0.6931818181818182"
  },
  {
    "cohortWeek": "2026-06-08",
    "weeksSinceSignup": "1",
    "cohortSize": "88",
    "activeUsers": "56",
    "retention": "0.6363636363636364"
  },
  {
    "cohortWeek": "2026-06-08",
    "weeksSinceSignup": "2",
    "cohortSize": "88",
    "activeUsers": "52",
    "retention": "0.5909090909090909"
  },
  {
    "cohortWeek": "2026-06-08",
    "weeksSinceSignup": "3",
    "cohortSize": "88",
    "activeUsers": "51",
    "retention": "0.5795454545454546"
  },
  {
    "cohortWeek": "2026-06-08",
    "weeksSinceSignup": "4",
    "cohortSize": "88",
    "activeUsers": "49",
    "retention": "0.5568181818181818"
  },
  {
    "cohortWeek": "2026-06-08",
    "weeksSinceSignup": "5",
    "cohortSize": "88",
    "activeUsers": "48",
    "retention": "0.5454545454545454"
  },
  {
    "cohortWeek": "2026-06-08",
    "weeksSinceSignup": "6",
    "cohortSize": "88",
    "activeUsers": "45",
    "retention": "0.5113636363636364"
  },
  {
    "cohortWeek": "2026-06-15",
    "weeksSinceSignup": "0",
    "cohortSize": "91",
    "activeUsers": "61",
    "retention": "0.6703296703296703"
  },
  {
    "cohortWeek": "2026-06-15",
    "weeksSinceSignup": "1",
    "cohortSize": "91",
    "activeUsers": "63",
    "retention": "0.6923076923076923"
  },
  {
    "cohortWeek": "2026-06-15",
    "weeksSinceSignup": "2",
    "cohortSize": "91",
    "activeUsers": "62",
    "retention": "0.6813186813186813"
  },
  {
    "cohortWeek": "2026-06-15",
    "weeksSinceSignup": "3",
    "cohortSize": "91",
    "activeUsers": "57",
    "retention": "0.6263736263736264"
  },
  {
    "cohortWeek": "2026-06-15",
    "weeksSinceSignup": "4",
    "cohortSize": "91",
    "activeUsers": "56",
    "retention": "0.6153846153846154"
  },
  {
    "cohortWeek": "2026-06-15",
    "weeksSinceSignup": "5",
    "cohortSize": "91",
    "activeUsers": "56",
    "retention": "0.6153846153846154"
  },
  {
    "cohortWeek": "2026-06-15",
    "weeksSinceSignup": "6",
    "cohortSize": "91",
    "activeUsers": "51",
    "retention": "0.5604395604395604"
  },
  {
    "cohortWeek": "2026-06-22",
    "weeksSinceSignup": "0",
    "cohortSize": "72",
    "activeUsers": "45",
    "retention": "0.625"
  },
  {
    "cohortWeek": "2026-06-22",
    "weeksSinceSignup": "1",
    "cohortSize": "72",
    "activeUsers": "51",
    "retention": "0.7083333333333334"
  },
  {
    "cohortWeek": "2026-06-22",
    "weeksSinceSignup": "2",
    "cohortSize": "72",
    "activeUsers": "40",
    "retention": "0.5555555555555556"
  },
  {
    "cohortWeek": "2026-06-22",
    "weeksSinceSignup": "3",
    "cohortSize": "72",
    "activeUsers": "39",
    "retention": "0.5416666666666666"
  },
  {
    "cohortWeek": "2026-06-22",
    "weeksSinceSignup": "4",
    "cohortSize": "72",
    "activeUsers": "36",
    "retention": "0.5"
  },
  {
    "cohortWeek": "2026-06-22",
    "weeksSinceSignup": "5",
    "cohortSize": "72",
    "activeUsers": "36",
    "retention": "0.5"
  },
  {
    "cohortWeek": "2026-06-22",
    "weeksSinceSignup": "6",
    "cohortSize": "72",
    "activeUsers": "36",
    "retention": "0.5"
  },
  {
    "cohortWeek": "2026-06-29",
    "weeksSinceSignup": "0",
    "cohortSize": "94",
    "activeUsers": "61",
    "retention": "0.648936170212766"
  },
  {
    "cohortWeek": "2026-06-29",
    "weeksSinceSignup": "1",
    "cohortSize": "94",
    "activeUsers": "66",
    "retention": "0.7021276595744681"
  },
  {
    "cohortWeek": "2026-06-29",
    "weeksSinceSignup": "2",
    "cohortSize": "94",
    "activeUsers": "57",
    "retention": "0.6063829787234043"
  },
  {
    "cohortWeek": "2026-06-29",
    "weeksSinceSignup": "3",
    "cohortSize": "94",
    "activeUsers": "56",
    "retention": "0.5957446808510638"
  },
  {
    "cohortWeek": "2026-06-29",
    "weeksSinceSignup": "4",
    "cohortSize": "94",
    "activeUsers": "58",
    "retention": "0.6170212765957447"
  },
  {
    "cohortWeek": "2026-06-29",
    "weeksSinceSignup": "5",
    "cohortSize": "94",
    "activeUsers": "56",
    "retention": "0.5957446808510638"
  },
  {
    "cohortWeek": "2026-06-29",
    "weeksSinceSignup": "6",
    "cohortSize": "94",
    "activeUsers": "49",
    "retention": "0.5212765957446809"
  }
]
```

## Related

- [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.
- [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-product-analytics](https://hypequery.com/clickhouse-product-analytics)
- [/clickhouse-saas-analytics](https://hypequery.com/clickhouse-saas-analytics)
- [/clickhouse-typescript](https://hypequery.com/clickhouse-typescript)