---
title: "ClickHouse gaming analytics"
excerpt: "Games generate event data at a scale and velocity most analytics systems can't handle in real time: millions of players producing telemetry every second, leaderboards that need to be current within seconds, and economy data that drives live balance decisions. ClickHouse is the database behind several of the largest gaming analytics platforms."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-06-08 00:00:00"
publishedOn: "2026-06-08 00:00:00"
updatedOn: "2026-06-08 00:00:00"
status: "published"
---

Games produce more event data per user than almost any other category of software. A multiplayer game with 100,000 concurrent players can generate millions of events per second: movement, combat, economy transactions, social interactions, matchmaking signals. The analytics layer needs to keep up with that throughput while answering questions about player behavior fast enough to be useful for live operations.

ClickHouse® handles this workload well. Its columnar storage and vectorized execution process high-cardinality event streams efficiently, and its built-in approximate aggregation functions (HyperLogLog for unique counts, T-Digest for percentiles) make billion-row queries practical for operational dashboards. Game studios building [real-time leaderboards](https://www.tinybird.co/blog/building-real-time-leaderboards-with-tinybird) and player analytics use it as their primary analytical store.

This post covers the schemas, queries, and patterns for telemetry ingestion, leaderboards, player lifecycle, economy analytics, and live operations.

## Player event schema

The core table captures all player activity as an append-only event log. Avoid the temptation to normalize this into many tables; for analytics queries, a wide event table with `LowCardinality` on dimension columns is faster than joins:

```sql
CREATE TABLE player_events
(
    event_time      DateTime64(3),
    event_type      LowCardinality(String),  -- 'match_start', 'kill', 'death', 'purchase', 'level_up'
    player_id       String,
    session_id      String,
    game_mode       LowCardinality(String),  -- 'ranked', 'casual', 'co-op'
    platform        LowCardinality(String),  -- 'pc', 'ps5', 'xbox', 'mobile'
    region          LowCardinality(String),
    match_id        Nullable(String),
    item_id         Nullable(String),
    amount          Nullable(Float64),       -- for purchases: USD value
    currency        LowCardinality(Nullable(String)),  -- 'usd', 'gems', 'gold'
    properties      String                   -- JSON for event-specific data
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, toStartOfHour(event_time), player_id);
```

`LowCardinality` on `event_type`, `game_mode`, `platform`, and `region` is non-optional at scale. These columns appear in nearly every dashboard query and every WHERE clause. Without dictionary encoding, GROUP BY on string columns at billion-event scale is 3-5x slower.

## Real-time leaderboards

Leaderboards are a classic ClickHouse use case: aggregate scores across many players, rank them, return the top N, and do it fast enough for sub-second refresh. The architecture that works at scale uses a `ReplacingMergeTree` for current scores and queries it with `FINAL` or the `argMax` pattern:

```sql
CREATE TABLE player_scores
(
    player_id       String,
    game_mode       LowCardinality(String),
    season_id       String,
    score           Int64,
    kills           UInt32,
    deaths          UInt32,
    wins            UInt32,
    losses          UInt32,
    updated_at      DateTime64(3),
    _version        UInt64
)
ENGINE = ReplacingMergeTree(_version)
ORDER BY (season_id, game_mode, player_id);
```

Leaderboard query using `argMax` for exact current scores without waiting for background deduplication:

```sql
SELECT
    player_id,
    argMax(score, updated_at)   AS current_score,
    argMax(kills, updated_at)   AS kills,
    argMax(wins, updated_at)    AS wins,
    rank() OVER (
        PARTITION BY game_mode
        ORDER BY argMax(score, updated_at) DESC
    )                           AS rank
FROM player_scores
WHERE season_id = '2026-s3'
  AND game_mode = 'ranked'
GROUP BY player_id, game_mode
ORDER BY current_score DESC
LIMIT 100;
```

For the surrounding-rank query (show a player their position plus the 5 players above and below them):

```sql
WITH ranked AS (
    SELECT
        player_id,
        argMax(score, updated_at)   AS score,
        row_number() OVER (ORDER BY argMax(score, updated_at) DESC) AS rank
    FROM player_scores
    WHERE season_id = '2026-s3' AND game_mode = 'ranked'
    GROUP BY player_id
),
my_rank AS (
    SELECT rank FROM ranked WHERE player_id = {{ String(player_id, required=True) }}
)
SELECT r.player_id, r.score, r.rank
FROM ranked AS r
CROSS JOIN my_rank AS m
WHERE abs(r.rank - m.rank) <= 5
ORDER BY r.rank;
```

## Player lifecycle and retention

Player retention is the most important metric in any live service game. ClickHouse computes it efficiently in a single query across full player histories:

```sql
SELECT
    toStartOfWeek(first_session)                            AS cohort_week,
    dateDiff('week', first_session, session_week)           AS weeks_since_first,
    uniq(player_id)                                         AS retained_players,
    cohort_size,
    round(uniq(player_id) / cohort_size * 100, 1)          AS retention_rate
FROM (
    SELECT
        player_id,
        toStartOfWeek(min(event_time)) OVER (PARTITION BY player_id) AS first_session,
        toStartOfWeek(event_time)                                      AS session_week
    FROM player_events
    WHERE event_type = 'session_start'
) AS sessions
INNER JOIN (
    SELECT
        toStartOfWeek(min(event_time)) AS cohort_week,
        uniq(player_id)               AS cohort_size
    FROM player_events
    WHERE event_type = 'session_start'
    GROUP BY cohort_week
) AS cohorts USING (cohort_week)
WHERE weeks_since_first <= 8
GROUP BY cohort_week, weeks_since_first, cohort_size
ORDER BY cohort_week, weeks_since_first;
```

Day-1, day-7, and day-30 retention broken out by acquisition channel and platform tells you which installs produce players worth spending to acquire. At the volume games generate, [real-time data visualization](https://www.tinybird.co/blog/real-time-data-visualization) of retention curves needs to query pre-aggregated rollup tables rather than raw events.

## In-game economy analytics

Virtual economy health is critical for live service games. Inflation (too much currency entering the economy), deflation (too much leaving), and price manipulation all show up in transaction patterns before they become player-visible problems:

```sql
SELECT
    toStartOfHour(event_time)       AS hour,
    currency,
    sumIf(amount, amount > 0)       AS currency_minted,
    abs(sumIf(amount, amount < 0))  AS currency_burned,
    sum(amount)                     AS net_flow,
    uniq(player_id)                 AS active_traders
FROM player_events
WHERE event_type IN ('purchase', 'reward', 'trade', 'spend')
  AND currency IS NOT NULL
  AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour, currency
ORDER BY hour DESC, currency;
```

Identifying economy outliers: players with abnormal acquisition rates that might indicate exploits:

```sql
WITH avg_stats AS (
    SELECT
        currency,
        avg(earned_24h) AS avg_earned,
        stddevPop(earned_24h) AS stddev_earned
    FROM (
        SELECT
            player_id,
            currency,
            sumIf(amount, amount > 0) AS earned_24h
        FROM player_events
        WHERE event_type = 'reward'
          AND event_time >= today() - 7
          AND currency IS NOT NULL
        GROUP BY player_id, currency
    )
    GROUP BY currency
)
SELECT
    p.player_id,
    p.currency,
    p.earned_24h,
    a.avg_earned,
    round((p.earned_24h - a.avg_earned) / nullIf(a.stddev_earned, 0), 2) AS z_score
FROM (
    SELECT player_id, currency, sumIf(amount, amount > 0) AS earned_24h
    FROM player_events
    WHERE event_type = 'reward'
      AND event_time >= today()
      AND currency IS NOT NULL
    GROUP BY player_id, currency
) AS p
INNER JOIN avg_stats AS a USING (currency)
WHERE abs((p.earned_24h - a.avg_earned) / nullIf(a.stddev_earned, 0)) > 4
ORDER BY z_score DESC;
```

## Matchmaking and session analytics

For live operations teams monitoring match health and queue times in real time:

```sql
SELECT
    toStartOfMinute(event_time)     AS minute,
    game_mode,
    region,
    count()                         AS matches_started,
    avg(
        JSONExtractFloat(properties, 'queue_time_sec')
    )                               AS avg_queue_sec,
    quantile(0.95)(
        JSONExtractFloat(properties, 'queue_time_sec')
    )                               AS p95_queue_sec,
    avg(
        JSONExtractFloat(properties, 'match_duration_sec')
    )                               AS avg_match_duration_sec
FROM player_events
WHERE event_type = 'match_start'
  AND event_time >= now() - INTERVAL 30 MINUTE
GROUP BY minute, game_mode, region
ORDER BY minute DESC, matches_started DESC;
```

For detecting matchmaking degradation early, a materialized view computes rolling averages by region and game mode:

```sql
CREATE MATERIALIZED VIEW matchmaking_stats_mv TO matchmaking_stats AS
SELECT
    toStartOfFiveMinutes(event_time)    AS window,
    game_mode,
    region,
    count()                             AS matches,
    avgState(JSONExtractFloat(properties, 'queue_time_sec')) AS avg_queue_state,
    quantileState(0.95)(JSONExtractFloat(properties, 'queue_time_sec')) AS p95_queue_state
FROM player_events
WHERE event_type = 'match_start'
GROUP BY window, game_mode, region;
```

## Content performance and game balance

For game designers evaluating which content players engage with:

```sql
SELECT
    item_id,
    count()                                             AS interactions,
    uniq(player_id)                                     AS unique_players,
    countIf(event_type = 'purchase')                    AS purchases,
    round(sumIf(amount, event_type = 'purchase'), 2)    AS revenue_usd,
    round(
        countIf(event_type = 'purchase') /
        uniq(player_id) * 100, 2
    )                                                   AS purchase_rate
FROM player_events
WHERE item_id IS NOT NULL
  AND event_time >= today() - 7
GROUP BY item_id
ORDER BY interactions DESC
LIMIT 50;
```

For [real-time streaming analytics architectures](https://www.tinybird.co/blog/real-time-streaming-data-architectures-that-scale) behind live game events (battle passes, seasonal content, limited-time modes), pre-aggregate player engagement by content item into a daily rollup. The rollup stays small regardless of player scale.

## Player segmentation for live operations

Live service games run constant campaigns: seasonal events, win-back offers for churned players, targeted push notifications for players who haven't logged in this week. ClickHouse computes segment membership at query time, fast enough to use as a real-time targeting signal:

```sql
-- Players who were active 8-30 days ago but not in the last 7 days (lapsing)
SELECT
    player_id,
    max(event_time)                         AS last_seen,
    dateDiff('day', max(event_time), now())  AS days_inactive,
    countIf(event_type = 'purchase')         AS lifetime_purchases,
    round(sumIf(amount, event_type = 'purchase'), 2) AS lifetime_spend_usd
FROM player_events
WHERE event_time >= today() - 30
GROUP BY player_id
HAVING days_inactive BETWEEN 7 AND 30
ORDER BY lifetime_spend_usd DESC;
```

For high-value lapsing players (lifetime spend > $50, inactive 7-30 days), a targeted re-engagement offer has significantly higher ROI than a blanket campaign. ClickHouse computes this segment over millions of players in seconds.

Engagement tier classification for personalized in-game experiences:

```sql
SELECT
    player_id,
    multiIf(
        sessions_30d >= 20 AND spend_30d >= 10, 'whale',
        sessions_30d >= 10 AND spend_30d >= 1,  'engaged_payer',
        sessions_30d >= 10,                      'engaged_free',
        sessions_30d >= 3,                       'casual',
        'at_risk'
    ) AS segment,
    sessions_30d,
    round(spend_30d, 2) AS spend_usd_30d
FROM (
    SELECT
        player_id,
        countIf(event_type = 'session_start') AS sessions_30d,
        sumIf(amount, event_type = 'purchase') AS spend_30d
    FROM player_events
    WHERE event_time >= today() - 30
    GROUP BY player_id
)
ORDER BY spend_usd_30d DESC;
```

This segment classification runs in under a second over millions of players, enabling real-time targeting without batch segmentation jobs. For [real-time recommendation engine](https://www.tinybird.co/blog/real-time-recommendation-engine-ai) use cases, the segment query becomes an endpoint your game server calls when a player logs in to determine which offer or content to surface.

## Tinybird for gaming

Game analytics has a scale problem most analytics tools aren't designed for. A live service title with 1 million daily active players generating 50 events per session at 3 sessions per player produces 150 million events per day, with peaks during primetime or seasonal events that are 5-10x the baseline. The ingestion path and query layer both need to handle that variance without manual scaling intervention.

Tinybird is managed ClickHouse with a streaming Events API for [user-facing analytics](https://www.tinybird.co/blog/user-facing-analytics) at player scale. The Events API accepts batched NDJSON over HTTP and makes ingested data queryable within seconds of arrival. Your game backend sends events directly to the endpoint. No Kafka required for standard telemetry volumes, no consumer group management, no batching infrastructure.

The leaderboard endpoint becomes a Tinybird Pipe. The player segment query becomes a Pipe. The economy anomaly detection becomes a Pipe. Each Pipe is a parameterized HTTP endpoint your game server, live ops dashboard, and recommendation system call directly. Consider [the 8 considerations for designing public data APIs](https://www.tinybird.co/blog/8-considerations-for-designing-public-data-apis) when structuring these endpoints: parameter validation, rate limiting, and caching are all handled at the Tinybird layer.

Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production with no caching layer. Leaderboard queries that scan millions of player scores and rank them run inside that same latency envelope. The surrounding-rank query, which needs to return before the session start screen renders, is exactly the kind of parameterized lookup Tinybird Pipes are designed for.

Tinybird handles cluster scaling, replication, and upgrades. During peak events (a major content drop, a tournament final), compute scales without configuration changes. Your live ops team sees real-time dashboards. Your players see sub-second leaderboards. The engineering team ships game features instead of maintaining analytics infrastructure.

{% cta
  title="Gaming analytics at player scale"
  text="Tinybird is managed ClickHouse with streaming ingestion and real-time SQL endpoints. Ship your game analytics API in hours."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}