---
title: "ClickHouse for analysts"
excerpt: "Analysts use ClickHouse SQL for ad-hoc queries, funnels, and dashboards over high-volume event data without waiting on ETL."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-05-25 00:00:00"
publishedOn: "2026-05-25 00:00:00"
updatedOn: "2026-05-25 00:00:00"
status: "published"
---

ClickHouse® is a columnar database built for exactly the queries analysts run most: aggregations across large date ranges, GROUP BY across multiple dimensions, COUNT DISTINCT on high-cardinality columns, and time-series rollups. Queries that take minutes in Postgres take milliseconds here.

ClickHouse is "a high-performance, column-oriented SQL database management system (DBMS) for online analytical processing (OLAP)." Unlike row-store databases, where even a query touching two columns reads entire rows from disk, ClickHouse stores each column independently. A query that only needs `event_time`, `event_type`, and `user_id` reads exactly those three columns and nothing else.

It runs standard SQL. If you know SQL, you can query ClickHouse today. There are a few extensions worth knowing, a few patterns that unlock the most common analyst workflows, and a few gotchas that catch people coming from row-store databases. This post covers all three.

If you're using [Tinybird](https://www.tinybird.co/), managed ClickHouse with a SQL editor and API layer, every example here runs exactly as written in the Tinybird workspace.

## What ClickHouse is fast at

The queries that are painful in row-store databases are ClickHouse's native workload. If you're coming from a warehouse like BigQuery or Snowflake, the positioning is different, ClickHouse is optimized for [analytics at scale](https://www.tinybird.co/blog/analytics-at-scale) rather than scheduled batch queries:

```sql
-- Count distinct users who performed an action, by day, last 90 days
SELECT
    toDate(event_time)      AS day,
    event_type,
    count()                 AS events,
    uniq(user_id)           AS unique_users
FROM events
WHERE event_time >= now() - INTERVAL 90 DAY
GROUP BY day, event_type
ORDER BY day;
```

This query scans 90 days of event data across potentially billions of rows. In ClickHouse, with a well-designed schema, it returns in under a second. The reason is physical: because column values are stored sequentially on disk, only the four referenced columns are read. Everything else stays compressed and unread.

This block-wise storage alignment is specifically what makes analytical queries faster, no unnecessary data is loaded, compared to row-based storage, where entire rows including irrelevant columns are always read.

## The SQL you already know, plus some useful extensions

ClickHouse supports the SQL standard you're used to: `SELECT`, `WHERE`, `GROUP BY`, `ORDER BY`, `JOIN`, `HAVING`, window functions, CTEs. A few additions are worth learning early.

### `uniq()` instead of `COUNT(DISTINCT ...)`

`COUNT(DISTINCT col)` in standard SQL is exact but expensive at scale. ClickHouse's `uniq()` uses a HyperLogLog sketch, approximately 2% error, but orders of magnitude faster on large datasets. For even faster approximation with slightly less accuracy, `uniqHLL12()` uses a smaller sketch:

```sql
-- Approximate (fast, ~2% error)
SELECT uniq(user_id) AS unique_users FROM events;

-- Even faster approximation (useful on very large tables)
SELECT uniqHLL12(user_id) AS unique_users FROM events;

-- Exact (slower, uses more memory)
SELECT uniqExact(user_id) AS unique_users FROM events;
```

For most analyst use cases, understanding scale, spotting trends, comparing segments, the approximation is fine. Use `uniqExact()` when you need the precise number, such as billing reconciliation or compliance reporting.

### `countIf()` and `sumIf()` for conditional aggregation

Instead of multiple subqueries or CASE statements, use the `If` variants. One pass over the data, multiple conditional aggregations:

```sql
SELECT
    campaign_id,
    countIf(event_type = 'impression') AS impressions,
    countIf(event_type = 'click')      AS clicks,
    round(
        countIf(event_type = 'click') /
        nullIf(countIf(event_type = 'impression'), 0) * 100,
        2
    )                                  AS ctr_pct,
    sumIf(revenue, event_type = 'conversion') AS revenue
FROM ad_events
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY campaign_id
ORDER BY impressions DESC;
```

`nullIf(x, 0)` prevents division by zero when a campaign has impressions but no clicks, returning NULL instead of an error.

### `PREWHERE` for selective filters

ClickHouse's `PREWHERE` is a performance optimization not available in standard SQL. It reads only the filter column first, identifies matching rows, and then reads the remaining columns only for those rows:

```sql
-- Standard WHERE: reads all columns for all rows, then filters
SELECT user_id, revenue FROM events
WHERE event_type = 'conversion';

-- PREWHERE: reads event_type first, then user_id/revenue only for matches
SELECT user_id, revenue FROM events
PREWHERE event_type = 'conversion';
```

On a table with many columns where the filter is highly selective, `PREWHERE` can cut I/O dramatically. On a table with many columns where the filter is highly selective, `PREWHERE` can cut I/O dramatically. In Tinybird, the query optimizer applies it automatically in many cases, but knowing the concept helps you write queries that the optimizer can work with.

### Date and time functions

ClickHouse has a rich set of date functions more ergonomic than standard SQL:

```sql
-- Truncate to time buckets (useful for GROUP BY)
toStartOfHour(event_time)
toStartOfDay(event_time)
toStartOfWeek(event_time)    -- Monday
toStartOfMonth(event_time)

-- Relative time filters
WHERE event_time >= now() - INTERVAL 7 DAY
WHERE event_time >= today() - 30
WHERE toDate(event_time) = yesterday()

-- Extract components
toDayOfWeek(event_time)   -- 1 = Monday, 7 = Sunday
toHour(event_time)        -- 0-23
toISOWeek(event_time)     -- ISO week number
```

### Array and JSON functions for semi-structured data

If your events store extra attributes as JSON or arrays:

```sql
-- Extract typed fields from JSON
SELECT
    JSONExtractString(properties, 'plan')    AS plan,
    JSONExtractInt(properties, 'seat_count') AS seats,
    JSONExtractFloat(properties, 'mrr')      AS mrr
FROM accounts;

-- Flatten arrays into rows (useful for tag analysis)
SELECT arrayJoin(tags) AS tag, count() AS cnt
FROM content
GROUP BY tag
ORDER BY cnt DESC;
```

In Tinybird, the workspace UI shows column types and lets you autocomplete JSON paths once your datasource is loaded, useful when exploring event schemas you didn't write yourself.

## Funnel analysis

Funnel queries are one of the most common analyst requests. ClickHouse handles them efficiently with `countIf` and the built-in `windowFunnel` function.

A simple step-by-step funnel:

```sql
SELECT
    countIf(event = 'signed_up')                  AS step_1_signup,
    countIf(event = 'onboarding_completed')        AS step_2_onboarding,
    countIf(event = 'first_query_run')             AS step_3_first_query,
    round(countIf(event = 'onboarding_completed') /
          nullIf(countIf(event = 'signed_up'), 0) * 100, 1) AS step1_to_step2_pct,
    round(countIf(event = 'first_query_run') /
          nullIf(countIf(event = 'onboarding_completed'), 0) * 100, 1) AS step2_to_step3_pct
FROM user_events
WHERE event_time >= now() - INTERVAL 30 DAY;
```

For strict ordered funnels, where a user must complete step N before counting for step N+1, use `windowFunnel`:

```sql
SELECT level, count() AS users
FROM (
    SELECT
        user_id,
        windowFunnel(7 * 86400)(   -- 7-day window in seconds
            event_time,
            event = 'signed_up',
            event = 'onboarding_completed',
            event = 'first_query_run',
            event = 'invited_teammate'
        ) AS level
    FROM user_events
    WHERE event_time >= now() - INTERVAL 90 DAY
    GROUP BY user_id
)
GROUP BY level
ORDER BY level;
```

`windowFunnel` returns the deepest step reached within the time window. In Tinybird, this becomes a Pipe endpoint your product team calls with `?days=30` or `?days=90`, no SQL access required on their side.

## Cohort retention analysis

Retention is a weekly or monthly analysis in most BI tools. In ClickHouse, a full cohort retention table runs in seconds even with years of history:

```sql
WITH
    cohorts AS (
        SELECT user_id, toStartOfWeek(min(event_time)) AS cohort_week
        FROM user_events
        WHERE event = 'signed_up'
        GROUP BY user_id
    ),
    activity AS (
        SELECT DISTINCT user_id, toStartOfWeek(event_time) AS active_week
        FROM user_events
        WHERE event_time >= now() - INTERVAL 52 WEEK
    )
SELECT
    c.cohort_week,
    dateDiff('week', c.cohort_week, a.active_week) AS weeks_since_signup,
    count(DISTINCT a.user_id)                       AS retained_users,
    count(DISTINCT c2.user_id)                      AS cohort_size,
    round(count(DISTINCT a.user_id) /
          nullIf(count(DISTINCT c2.user_id), 0) * 100, 1) AS retention_pct
FROM cohorts c
JOIN cohorts c2 ON c.cohort_week = c2.cohort_week
JOIN activity a ON c.user_id = a.user_id
WHERE dateDiff('week', c.cohort_week, a.active_week) BETWEEN 0 AND 12
GROUP BY c.cohort_week, weeks_since_signup
ORDER BY c.cohort_week, weeks_since_signup;
```

## Percentile and distribution analysis

Standard SQL's `AVG` hides a lot. For latency, revenue, and engagement, percentiles tell a more honest story:

```sql
SELECT
    endpoint,
    count()                         AS requests,
    avg(duration_ms)                AS avg_ms,
    quantile(0.50)(duration_ms)     AS p50_ms,
    quantile(0.90)(duration_ms)     AS p90_ms,
    quantile(0.99)(duration_ms)     AS p99_ms
FROM api_requests
WHERE timestamp >= now() - INTERVAL 24 HOUR
GROUP BY endpoint
ORDER BY p99_ms DESC;
```

`quantile()` uses T-Digest approximation. For exact percentiles in SLA reporting, use `quantileExact()`.

## Period-over-period comparison with `lagInFrame`

`lagInFrame` lets you access the previous period's value for period-over-period comparisons without a self-join:

```sql
SELECT
    day,
    revenue,
    lagInFrame(revenue, 1, 0) OVER (ORDER BY day) AS prev_day_revenue,
    round(
        (revenue - lagInFrame(revenue, 1, 0) OVER (ORDER BY day)) /
        nullIf(lagInFrame(revenue, 1, 0) OVER (ORDER BY day), 0) * 100,
        1
    ) AS pct_change
FROM daily_revenue
ORDER BY day;
```

This is cleaner than a self-join and significantly faster on large tables.

## Window functions for rankings and running totals

ClickHouse supports full SQL window function syntax:

```sql
-- 7-day rolling average revenue
SELECT
    day,
    revenue,
    round(avg(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_7d_avg
FROM daily_revenue
ORDER BY day;

-- Rank users by spend within each plan tier
SELECT
    user_id,
    plan,
    total_spend,
    rank() OVER (PARTITION BY plan ORDER BY total_spend DESC) AS spend_rank
FROM user_spend
WHERE month = '2026-05';
```

## Reading query performance

ClickHouse returns statistics with every result. In the Tinybird query editor, check `elapsed`, `rows_read`, and `bytes_read` at the bottom of every query run:

```json
{
  "statistics": {
    "elapsed": 0.043,
    "rows_read": 4820000,
    "bytes_read": 96400000
  }
}
```

43ms to scan 4.8 million rows. If `rows_read` is much higher than you expected, your query is scanning too much. Common causes:

- A function wraps an indexed column: `toDate(timestamp) = '2026-05-25'` disables the index. Rewrite as `timestamp >= '2026-05-25' AND timestamp < '2026-05-26'`
- The time filter column isn't first in the table's `ORDER BY` key
- No partition key, so a monthly filter still reads the whole table

## Sampling for fast exploration

When exploring a large dataset for the first time, use `SAMPLE` to get a representative subset without waiting for a full scan:

```sql
-- Query 1% of the data, scale results ×100
SELECT event_type, count() * 100 AS estimated_count
FROM events SAMPLE 0.01
WHERE event_time >= now() - INTERVAL 30 DAY
GROUP BY event_type
ORDER BY estimated_count DESC;
```

Results scale linearly. This is how you explore a 10-billion-row dataset in under a second before deciding which query to optimize. More patterns are in [how to make SQL database faster](https://www.tinybird.co/blog/how-to-make-sql-database-faster).

## Sharing analysis with your team via Tinybird

One of the recurring friction points for analysts is re-running the same query when stakeholders ask for slightly different cuts of the data. Tinybird solves this with Pipes: SQL queries you write once and publish as parameterized HTTP endpoints.

Write the cohort analysis query, publish it as an endpoint:

```sql
-- retention_by_cohort.pipe
NODE cohorts
SQL >
  SELECT user_id, toStartOfWeek(min(event_time)) AS cohort_week
  FROM user_events
  WHERE event = 'signed_up'
  GROUP BY user_id

NODE retention
SQL >
  %
  SELECT
      c.cohort_week,
      dateDiff('week', c.cohort_week, a.active_week) AS week_number,
      round(count(DISTINCT a.user_id) /
            nullIf(count(DISTINCT c2.user_id), 0) * 100, 1) AS retention_pct
  FROM cohorts c
  JOIN cohorts c2 ON c.cohort_week = c2.cohort_week
  JOIN (
      SELECT DISTINCT user_id, toStartOfWeek(event_time) AS active_week
      FROM user_events
      WHERE event_time >= now() - INTERVAL {{ Int32(weeks, 12) }} WEEK
  ) a ON c.user_id = a.user_id
  WHERE week_number BETWEEN 0 AND {{ Int32(max_week, 8) }}
  GROUP BY c.cohort_week, week_number
  ORDER BY c.cohort_week, week_number

TYPE ENDPOINT
```

Your product team calls `retention_by_cohort.json?weeks=24&max_week=12` themselves. Your dashboard connects to the same URL. You update the SQL once and everyone gets fresh results. No Slack message asking you to re-run the pivot table.

Tinybird also connects to BI tools, [Grafana](https://www.tinybird.co/blog/clickhouse-grafana-example), Metabase, Superset, via its ClickHouse-compatible query interface. If your team already has a dashboard tool, you can point it at Tinybird and get ClickHouse performance without changing workflows.

## What ClickHouse is NOT good at

**Point lookups are slower than Postgres.** Fetching a single row by primary key is what Postgres is optimized for. ClickHouse's strength is scanning millions of rows, not returning one.

**Updates and deletes are expensive.** ClickHouse is append-first. `ALTER TABLE ... DELETE` and `UPDATE` are asynchronous and heavyweight. Don't use it for data that changes frequently at the row level.

**JOINs on two large tables require care.** ClickHouse loads the right-side table into memory during a hash join. Filter aggressively before joining, and put the smaller table on the right:

```sql
SELECT e.user_id, u.plan
FROM events e
INNER JOIN (
    SELECT user_id, plan FROM users WHERE plan IN ('pro', 'enterprise')
) u ON e.user_id = u.user_id
WHERE e.event_time >= now() - INTERVAL 30 DAY;
```

{% cta
  title="Query your data at ClickHouse speed"
  text="Tinybird gives analysts a SQL editor backed by managed ClickHouse®. No cluster setup, no infra tickets. Share queries as live API endpoints your team can call directly."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}