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 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:
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:
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:
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):
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:
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 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:
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:
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:
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:
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:
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 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:
-- 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:
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 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 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 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.