ClickHouse SQL cookbook

These recipes assume a product_events collection with an event name, durable user and account IDs, and purpose-specific numeric properties. Adapt names and definitions to your tracking plan; a syntactically correct query can still answer the wrong question.

GraphJSON collections expose:

• timestamp: event time as a Unix second
• json: the serialized event payload

Start with Run SQL queries if those columns or ClickHouse JSON extraction are new to you.

Create a reusable event base#

Common table expressions keep extraction logic in one place:

WITH events AS (
  SELECT
    toDateTime(timestamp, 'UTC') AS occurred_at,
    JSONExtractString(json, 'event') AS event,
    JSONExtractString(json, 'event_id') AS event_id,
    JSONExtractString(json, 'user_id') AS user_id,
    JSONExtractString(json, 'account_id') AS account_id,
    JSONExtractString(json, 'plan') AS plan
  FROM product_events
  WHERE toDateTime(timestamp) >= subtractDays(now(), 30)
)
SELECT event, count() AS events
FROM events
GROUP BY event
ORDER BY events DESC

Filter the source table’s time range before expensive grouping or joins. Use a named IANA time zone when local calendar boundaries matter.

Daily active accounts#

Count an account once per day when it performs one of the actions your team defines as meaningful:

WITH active_events AS (
  SELECT
    toDate(toDateTime(timestamp, 'America/Los_Angeles')) AS day,
    JSONExtractString(json, 'account_id') AS account_id
  FROM product_events
  WHERE
    JSONExtractString(json, 'event') IN (
      'dashboard_created',
      'report_exported',
      'api_key_used'
    )
    AND toDateTime(timestamp) >= subtractDays(now(), 30)
)
SELECT
  toUnixTimestamp(toDateTime(day, 'America/Los_Angeles')) AS timestamp,
  uniqExact(account_id) AS active_accounts
FROM active_events
WHERE account_id != ''
GROUP BY day
ORDER BY day

Name the saved query after the definition, such as daily_active_accounts_key_actions. “Active” is not self-explanatory.

Rolling 7-day active users#

A rolling window is not the sum of seven daily unique counts because the same person can appear on several days. Build one row for each reporting day and count users in its trailing window:

WITH
  toDate(now()) AS end_day,
  activity AS (
    SELECT
      toDate(toDateTime(timestamp, 'UTC')) AS activity_day,
      JSONExtractString(json, 'user_id') AS user_id
    FROM product_events
    WHERE
      JSONExtractString(json, 'event') = 'product_used'
      AND toDateTime(timestamp) >= subtractDays(now(), 37)
  ),
  expanded AS (
    SELECT
      user_id,
      addDays(activity_day, offset) AS day
    FROM activity
    ARRAY JOIN range(7) AS offset
  )
SELECT
  toUnixTimestamp(toDateTime(day, 'UTC')) AS timestamp,
  uniqExact(user_id) AS rolling_7d_users
FROM expanded
WHERE
  user_id != ''
  AND day BETWEEN end_day - 29 AND end_day
GROUP BY day
ORDER BY day

Each activity row contributes its user to that day and the following six reporting days. The extra seven days in the source scan allow the earliest displayed day to have a complete trailing window.

Percentiles for latency#

Use percentiles for skewed measurements such as request duration:

SELECT
  toUnixTimestamp(
    toStartOfHour(toDateTime(timestamp, 'UTC'))
  ) AS hour,
  quantileExact(0.50)(
    JSONExtractFloat(json, 'duration_ms')
  ) AS p50_ms,
  quantileExact(0.95)(
    JSONExtractFloat(json, 'duration_ms')
  ) AS p95_ms,
  quantileExact(0.99)(
    JSONExtractFloat(json, 'duration_ms')
  ) AS p99_ms
FROM api_events
WHERE
  JSONExtractString(json, 'event') = 'api_request_completed'
  AND toDateTime(timestamp) >= subtractDays(now(), 7)
GROUP BY hour
ORDER BY hour

quantileExact favors exactness. At high volume, ClickHouse’s approximate quantile functions can reduce work. Do not average already-computed p95 values; calculate the percentile from the underlying measurements for the interval.

Latest known account state#

If you record state-change events, argMax selects the value attached to the latest event time:

SELECT
  JSONExtractString(json, 'account_id') AS account_id,
  argMax(
    JSONExtractString(json, 'plan'),
    timestamp
  ) AS current_plan,
  max(timestamp) AS changed_at
FROM billing_events
WHERE
  JSONExtractString(json, 'event') = 'subscription_status_changed'
  AND JSONExtractString(json, 'account_id') != ''
GROUP BY account_id
ORDER BY changed_at DESC
LIMIT 500

If two state changes can share the same whole-second timestamp, add a source sequence or higher-resolution ordering property and use it in your state model. Analytics state should not replace your application database for authorization or billing decisions.

Deduplicate retried events#

Count one row per application-generated event_id:

WITH unique_events AS (
  SELECT
    JSONExtractString(json, 'event_id') AS event_id,
    any(JSONExtractString(json, 'event')) AS event
  FROM product_events
  WHERE
    toDateTime(timestamp) >= subtractDays(now(), 30)
    AND JSONExtractString(json, 'event_id') != ''
  GROUP BY event_id
)
SELECT event, count() AS events
FROM unique_events
GROUP BY event
ORDER BY events DESC

any is appropriate only when every retry with the same ID has the same immutable payload. For mutable updates, use the explicit latest-state pattern in Deduplicate mutable business events.

Conversion within a time window#

windowFunnel evaluates ordered conditions for each account:

WITH per_account AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    windowFunnel(7 * 24 * 60 * 60)(
      toDateTime(timestamp),
      JSONExtractString(json, 'event') = 'account_created',
      JSONExtractString(json, 'event') = 'data_source_connected',
      JSONExtractString(json, 'event') = 'dashboard_shared'
    ) AS step_reached
  FROM product_events
  WHERE
    toDateTime(timestamp) >= subtractDays(now(), 60)
    AND JSONExtractString(json, 'account_id') != ''
  GROUP BY account_id
)
SELECT
  countIf(step_reached >= 1) AS created,
  countIf(step_reached >= 2) AS connected_source,
  countIf(step_reached >= 3) AS shared_dashboard,
  round(100 * connected_source / nullIf(created, 0), 1)
    AS activation_rate_pct
FROM per_account

The funnel window begins with the first matched condition for an account. Decide whether repeated attempts, out-of-order events, and reactivation should count before using the result as an activation definition.

Cohort retention matrix#

This example defines the cohort as the account’s first account_created week and activity as a later product_used event:

WITH
  cohorts AS (
    SELECT
      JSONExtractString(json, 'account_id') AS account_id,
      toStartOfWeek(min(toDateTime(timestamp, 'UTC'))) AS cohort_week
    FROM product_events
    WHERE
      JSONExtractString(json, 'event') = 'account_created'
      AND JSONExtractString(json, 'account_id') != ''
    GROUP BY account_id
  ),
  activity AS (
    SELECT DISTINCT
      JSONExtractString(json, 'account_id') AS account_id,
      toStartOfWeek(toDateTime(timestamp, 'UTC')) AS activity_week
    FROM product_events
    WHERE
      JSONExtractString(json, 'event') = 'product_used'
      AND JSONExtractString(json, 'account_id') != ''
  )
SELECT
  cohorts.cohort_week,
  dateDiff('week', cohorts.cohort_week, activity.activity_week)
    AS week_number,
  uniqExact(activity.account_id) AS retained_accounts
FROM cohorts
INNER JOIN activity USING account_id
WHERE
  activity.activity_week >= cohorts.cohort_week
  AND week_number BETWEEN 0 AND 12
GROUP BY cohorts.cohort_week, week_number
ORDER BY cohorts.cohort_week, week_number

To calculate a rate, join the result to each cohort’s account count. Decide whether “week 0” requires an activity event in addition to signup; that choice changes the denominator.

Sessionize with an inactivity gap#

The query below begins a new session after 30 minutes without activity:

WITH ordered AS (
  SELECT
    JSONExtractString(json, 'user_id') AS user_id,
    timestamp,
    lagInFrame(timestamp, 1, timestamp) OVER (
      PARTITION BY user_id
      ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS previous_timestamp
  FROM product_events
  WHERE
    toDateTime(timestamp) >= subtractDays(now(), 7)
    AND JSONExtractString(json, 'user_id') != ''
),
numbered AS (
  SELECT
    user_id,
    timestamp,
    sum(
      if(timestamp - previous_timestamp > 1800, 1, 0)
    ) OVER (
      PARTITION BY user_id
      ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_number
  FROM ordered
)
SELECT
  user_id,
  session_number,
  min(timestamp) AS session_started_at,
  max(timestamp) AS session_ended_at,
  count() AS events
FROM numbered
GROUP BY user_id, session_number
ORDER BY session_started_at DESC
LIMIT 500

This scan only sees activity inside its seven-day boundary. Widen the source interval when sessions crossing the first boundary matter.

Revenue by account without currency errors#

Store money in minor units and group by currency:

SELECT
  JSONExtractString(json, 'account_id') AS account_id,
  JSONExtractString(json, 'currency') AS currency,
  sum(JSONExtractInt(json, 'amount')) AS revenue_minor_units
FROM billing_events
WHERE
  JSONExtractString(json, 'event') = 'payment_succeeded'
  AND toDateTime(timestamp) >= subtractMonths(now(), 3)
GROUP BY account_id, currency
ORDER BY revenue_minor_units DESC
LIMIT 500

Never add USD cents and EUR cents. Convert in an upstream financial model when a reporting currency is required, and preserve the conversion rate and effective date.

Make recipes production-ready#

Before saving a query:

• define the entity, event set, time zone, and interval
• exclude empty identifiers
• choose exact or approximate functions intentionally
• test boundary dates and duplicated rows
• compare a small interval to hand-verified source records
• cap raw output and aggregate for visualization
• document schema versions or historical exceptions

GraphJSON caps the outer query result at 2,000 rows. A chart should usually return tens or hundreds of aggregate points, not an event dump.