10 ClickHouse SQL Queries for Product Analytics

Point-and-click analysis is ideal for routine questions. SQL earns its place when the definition is specific to your product: activation requires three ordered steps, account health combines several actions, or revenue must separate currencies and refunds.

GraphJSON’s SQL notebook exposes each collection as a ClickHouse table with two columns:

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

The examples below use a product_events collection and consistent event, user_id, and account_id fields. Change those names to match your tracking plan.

1. Event volume by name#

Begin by learning what the collection actually contains:

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

Empty names and near-duplicates such as report_exported and report_export are instrumentation issues worth fixing before deeper analysis.

2. Daily activated accounts#

Count customer accounts, not raw events:

SELECT
  toUnixTimestamp(
    toStartOfDay(toDateTime(timestamp, 'UTC'))
  ) AS day,
  uniqExact(JSONExtractString(json, 'account_id'))
    AS activated_accounts
FROM product_events
WHERE
  JSONExtractString(json, 'event') = 'account_activated'
  AND JSONExtractString(json, 'account_id') != ''
  AND toDateTime(timestamp) >= subtractDays(now(), 30)
GROUP BY day
ORDER BY day

Use the reporting timezone your team has documented. The Unix day result can become the x-axis of a GraphJSON time-series visualization.

3. Daily active users#

Define the event set that represents meaningful activity:

SELECT
  toUnixTimestamp(
    toStartOfDay(toDateTime(timestamp, 'UTC'))
  ) AS day,
  uniqExact(JSONExtractString(json, 'user_id')) AS active_users
FROM product_events
WHERE
  JSONExtractString(json, 'event') IN (
    'report_created',
    'report_exported',
    'dashboard_shared'
  )
  AND JSONExtractString(json, 'user_id') != ''
  AND toDateTime(timestamp) >= subtractDays(now(), 30)
GROUP BY day
ORDER BY day

“Active” is a product definition, not a built-in truth. Save the query with a descriptive name such as daily_active_users_key_actions.

4. Collected revenue by currency#

Store money in minor units and keep currencies separate:

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

USD cents and EUR cents cannot be added safely. Convert in an upstream financial model when you need one reporting currency.

5. p50 and p95 API latency#

Averages hide slow tails:

SELECT
  JSONExtractString(json, 'route') AS route,
  quantileExact(0.50)(
    JSONExtractFloat(json, 'duration_ms')
  ) AS p50_ms,
  quantileExact(0.95)(
    JSONExtractFloat(json, 'duration_ms')
  ) AS p95_ms,
  count() AS requests
FROM api_events
WHERE
  JSONExtractString(json, 'event') = 'api_request_completed'
  AND toDateTime(timestamp) >= subtractDays(now(), 7)
GROUP BY route
ORDER BY requests DESC
LIMIT 25

Normalize routes before ingestion. /v1/reports/:id is a useful group; one distinct path for every report ID is not.

6. Funnel reach by account#

windowFunnel checks ordered steps inside a seven-day window:

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_published'
    ) AS step_reached
  FROM product_events
  WHERE
    toDateTime(timestamp) >= subtractDays(now(), 90)
    AND JSONExtractString(json, 'account_id') != ''
  GROUP BY account_id
)
SELECT
  countIf(step_reached >= 1) AS created,
  countIf(step_reached >= 2) AS connected,
  countIf(step_reached >= 3) AS published
FROM per_account

Decide how repeated attempts, reactivation, and out-of-order events should behave before calling the last value “activation.”

7. Current plan from state changes#

Use argMax to select the property from the latest event time:

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

This is useful for analysis. Your billing database remains the authority for access and invoices.

8. Daily server error rate#

Calculate a rate from the same event population:

SELECT
  toUnixTimestamp(
    toStartOfDay(toDateTime(timestamp, 'UTC'))
  ) AS day,
  round(
    100 * countIf(
      JSONExtractInt(json, 'status_code') >= 500
    ) / nullIf(count(), 0),
    2
  ) AS error_rate_pct
FROM api_events
WHERE
  JSONExtractString(json, 'event') = 'api_request_completed'
  AND toDateTime(timestamp) >= subtractDays(now(), 30)
GROUP BY day
ORDER BY day

Keep 4xx and 5xx definitions separate. Many 4xx responses are expected customer input errors rather than service failures.

9. Feature adoption by plan#

Count accounts that used a feature, not the number of clicks:

SELECT
  JSONExtractString(json, 'plan') AS plan,
  uniqExact(JSONExtractString(json, 'account_id'))
    AS adopting_accounts
FROM product_events
WHERE
  JSONExtractString(json, 'event') = 'key_feature_used'
  AND JSONExtractString(json, 'feature') = 'scheduled_export'
  AND JSONExtractString(json, 'account_id') != ''
  AND toDateTime(timestamp) >= subtractDays(now(), 30)
GROUP BY plan
ORDER BY adopting_accounts DESC

This uses plan at the time of the event. Grouping historical usage by the account’s current plan requires a latest-state join.

10. Deduplicate uncertain retries#

When retry attempts preserve one immutable event_id, count it once:

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

This pattern is valid only when every delivery attempt for an ID has the same payload. Mutable business state needs an explicit latest-value rule.

Turn queries into trusted metrics#

Before saving any query, write down:

• the entity being counted
• included and excluded events
• reporting time zone
• identity and duplicate behavior
• exact or approximate aggregation choice
• expected comparison source

Filter time ranges early, aggregate before large joins, and keep visualization outputs small. GraphJSON caps the outer result at 2,000 rows because a chart should receive aggregate points, not an unbounded event dump.

The ClickHouse SQL cookbook includes rolling active users, cohort retention, sessionization, and more detailed production notes.