Walkthrough: Real-time Game Leaderboard

"Design a real-time game leaderboard" is a showcase question for one data structure: the Redis sorted set. The interview tests whether you know why the obvious SQL design collapses, and whether you can handle durability and scale once the data lives in memory.

Step 1: Requirements

Functional:

1. Players submit scores during play.
2. Anyone can view the top 10 (global and per-period: daily/weekly).
3. A player can see their own rank ("#48,201 of 3M") even when nowhere near the top.

Non-functional:

• Near-real-time: a score should be reflected within ~1 second.
• Scale: 50M monthly players; peak ~10k score writes/sec during events.
• Rankings may be briefly approximate for viewers, but a player's own submitted score must not be lost.

Step 2: Core entities

Player, Leaderboard (id + period, e.g. global-alltime, daily-2026-08-01), ScoreEntry (player, leaderboard, score, updated_at).

Step 3: API

POST /v1/scores            { leaderboard_id, score }
GET  /v1/leaderboards/:id/top?k=10
GET  /v1/leaderboards/:id/rank/me

Player identity comes from the auth token — and score submissions should be server-validated (signed by the game server, not raw from the client), or the leaderboard becomes a cheating contest. One sentence on that shows product sense.

Step 4: Why the SQL design collapses

The naive table (player, score) with ORDER BY score DESC LIMIT 10 works — until you look at requirement 3. Rank for an arbitrary player means counting everyone above them: a scan of millions of rows, per request, on a table absorbing 10k writes/sec. Precomputing a rank column is worse: one score update shifts the rank of everyone below it. B-trees keep data sorted, but they don't maintain positions cheaply under writes. Say this failure analysis out loud before presenting the fix — diagnosing the problem is half the points.

Step 5: The sorted set design

A Redis sorted set keeps members ordered by score using a skip list (a layered linked list offering O(log n) inserts and lookups) plus a hash map for direct member access. It supports exactly this question's operations natively:

ZADD    daily:2026-08-01 5230 player:42     -- set score
ZINCRBY daily:2026-08-01 100  player:42     -- or increment
ZREVRANGE daily:2026-08-01 0 9 WITHSCORES   -- top 10
ZREVRANK  daily:2026-08-01 player:42        -- my exact rank

ZADD and ZREVRANK are O(log n) — rank for any player, on a 3M-member board, in microseconds, with no scan. That's the whole reason this structure exists in the design. 10k writes/sec is comfortable for a single Redis node (~100k+ ops/sec).

Write path: validate → ZINCRBY (or ZADD GT, keeping the max — pick your semantics: max vs latest vs cumulative, and say which the game wants) → also write to a durable store. Read path: straight from Redis.

Deep dive 1: Durability — Redis is memory

Redis alone can lose recent writes on a crash (its persistence options still trade some window of loss). The clean answer: write-through — every score lands in a durable DB (Postgres or DynamoDB) and Redis, with the DB as source of truth. If a Redis node dies, rebuild its sorted sets by replaying scores from the DB — the leaderboard is derived data, so recovery is a bulk reload, not data loss. The cheaper alternative (periodic snapshots, accept losing a few seconds) is worth naming with its trade-off; requirement "a player's score must not be lost" pushes us to write-through.

Deep dive 2: Periodic boards

Daily/weekly boards fall out of time-bucketed keys: writes for today go to daily:2026-08-01, and at midnight a new key simply starts. Old keys get a TTL (expire after, say, 30 days) after being archived to the DB for history. No migration jobs — the key name is the period.

Deep dive 3: Serving viewers and scaling one huge board

Thousands of spectators watching a tournament board don't need a push channel each: polling every 2–3 seconds is simpler and fine for most views; reserve SSE for a genuinely live spectator screen. Cache the top-10 response for a second to collapse the read stampede.

If one global board outgrows a single Redis node (memory or write throughput), two options: partition by score range (shard 1 holds top scores, queries for top-k hit one shard; ranks need per-shard offsets) or shard by player and merge (each shard keeps a sorted set of its players; top-k = merge each shard's top-k; exact global rank becomes expensive — approximate it or sum per-shard ZREVRANK results). Name the trade-off: range partitioning serves top-k cheaply but needs rebalancing; player sharding balances writes but complicates rank. For friend leaderboards, skip all of this: fetch your ~200 friends' scores by member lookup and sort in the app — small n needs no infrastructure.

Wrap-up

Recap: sorted sets give O(log n) top-k and arbitrary-player rank, which SQL fundamentally couldn't; write-through to a durable DB makes Redis rebuildable; time-bucketed keys give daily/weekly boards for free; viewers poll with a briefly-cached top-10. With more time: anti-cheat pipelines and cross-region boards with eventual merge.