Finish a match and the game tells you, instantly, that you've climbed to rank 48,213 of 50 million. Showing the top 10 is easy. Telling every player their exact rank, live, while thousands of scores change every second, is the part that breaks a relational database, and the part interviewers want to see you solve.
The game-leaderboard interview end to end: top 10, my rank and my neighbours within seconds of a match, at about 15,000 score updates a second; 50 million players fit a 5 GB Redis sorted set. Game servers write score events to Kafka (the truth), a writer applies ZINCRBY, and reads use ZREVRANK and ZREVRANGE. Deep dives show why COUNT(*) is O(rank) while a skip list is O(log N), how to break ties and dedupe retries by match_id, merging per-shard top 10s, approximate ranks for the long tail, and rebuilding the derived set.
Phase 1: Requirements (5 minutes)#
"Who submits scores: the game client or the game servers?"
"Game servers. Matches are played on our servers, and they report the result."
"Good, that rules out the most basic cheat: a phone posting score=999999. And does every player need an exact rank, or is 'top 5%' good enough for most?"
"Exact for the top players. Show me what it costs to make it exact for everyone."
- Record points when a match ends (a player's season score only goes up)
- Show the global top 10 (and top 100 on a full page)
- Show a player their own rank and score, plus the 5 players above and below
- Seasons: a new leaderboard every month; past seasons stay readable
- Out of scope for the core: friends-only and per-country boards (we'll discuss how they extend)
- Freshness: a finished match shows up in ranks within ~2 seconds
- Latency: top 10 and "my rank" in under 50 ms
- Correctness: a retried match report must never count twice
- Availability: the game keeps working if the leaderboard is degraded; scores are never lost
Phase 2: Estimation (5 minutes)#
- Daily active players: 25M, each playing 10 matches a day
- Score updates: 250M/day ÷ 86,400 s ≈ 2,900/s, evening peak ~5x ≈ 15,000/s
- Reads (top 10 on the home screen, "my rank" after a match): about 3x writes ≈ 45,000/s at peak
- Players with a score this season: 50M
- One entry in an in-memory sorted set (player ID, score, index overhead): ~100 B
- Whole season board: 50M × 100 B ≈ 5 GB: fits comfortably in one machine's memory
- Durable score history: 250M rows/day × ~50 B ≈ 12.5 GB/day, kept in a database or log, not in memory
"5 GB and 15,000 writes a second fits in a single in-memory sorted set. So the hard part isn't size, it's the rank query, and keeping it correct when reports are retried."
Phase 3: API (3 minutes)#
POST /v1/scores (game servers only, mutual TLS)
{ "match_id": "m_771", "season": "2026-10", "results": [ { "player_id": "p_42", "points": 30 }, ... ] }
→ 202 Accepted
GET /v1/leaderboards/2026-10/top?limit=10
→ 200 [ { "rank": 1, "player_id": "p_9", "name": "...", "score": 18420 }, ... ]
GET /v1/leaderboards/2026-10/players/p_42?around=5
→ 200 { "rank": 48213, "score": 2210, "above": [...], "below": [...] }match_id is the idempotency key: a game server that times out and resends the same match gets the same result, not double points.
Phase 4: High-level design (10 minutes)#
%% title: Leaderboard: an event log for truth, a sorted set for ranks
flowchart TD
GS@{ icon: "sd:gamepad", label: "Game servers<br/>report match results" }
SS["Score service<br/>validate · dedupe by match_id"]:::core
K@{ icon: "tech:kafka", label: "Kafka · score events" }
W["Leaderboard writer<br/>ZINCRBY per player"]:::service
R@{ icon: "tech:redis", label: "Redis sorted set<br/>lb:2026-10 · primary + replica" }
DB@{ icon: "tech:postgresql", label: "PostgreSQL<br/>season totals · source of truth" }
LS["Leaderboard service<br/>top N · my rank"]:::service
P@{ icon: "sd:phone", label: "Players' apps" }
GS --> SS
SS --> K
K --> W
W --> R
K -->|"durable totals"| DB
P --> LS
LS -->|"ZREVRANGE · ZREVRANK"| R
class GS,P client
class K queue
class R cache
class DB store- Report. The game server posts the match result. The score service checks it came from a trusted server and that the points are plausible.
- Log. It writes one event per player to Kafka, keyed by player ID so a player's updates stay in order. That's the durable record.
- Apply. The leaderboard writer runs
ZINCRBY lb:2026-10 30 p_42, an O(log N) update to the sorted set. A separate consumer updates season totals in PostgreSQL. - Read. "My rank" is
ZREVRANK lb:2026-10 p_42(O(log N)); the neighbours areZREVRANGEaround that rank; the top 10 isZREVRANGE lb:2026-10 0 9, cached for one second because everybody asks for it.
Each season gets its own key (lb:2026-10). "Reset" is just starting to write to a new key; the old one becomes read-only and is archived to the database after a few days.
Phase 5: Deep dives (15 minutes)#
Deep dive 1: Why the rank query needs a different data structure#
Why not just SQL?
Scores live in a table with an index on score. The top 10 is ORDER BY score DESC LIMIT 10, which is fast. What does "what's my rank?" cost for a player in the middle of 50 million?
One strong answer
- Rank means counting.
SELECT COUNT(*) FROM scores WHERE score > 2210has to walk every index entry above the player: about 48,000 for rank 48,213, and 25 million for a player in the middle. - At 45,000 reads a second that's billions of index entries scanned per second, and the index is churning under 15,000 updates a second too.
- A sorted set keeps counts in its structure. Redis implements it as a skip list whose links record how many elements they span, so finding a member's rank is O(log N): around 26 steps for 50 million, not 25 million.
Two details make it production-ready:
Ties. Two players on 2,210 points: who ranks higher? Usually whoever got there first. A sorted-set score is a 64-bit float with 53 bits of exact integer precision, so pack both into it: score × 2^22 + (season_end − time_reached). With seasons under 2^22 seconds (about 48 days) and points well under 2^31, it stays exact, and higher means "more points, or the same points earlier".
Idempotency. Kafka delivers at least once, and game servers retry. Before applying a match, the writer runs a small Lua script that does SET applied:m_771:p_42 1 NX EX 86400 and only calls ZINCRBY if the key was new. Both happen atomically on the same Redis node, so a redelivered event is a no-op.
Deep dive 2: Beyond one node, and exact vs approximate ranks#
One sorted set on one Redis primary handles our numbers. Interviewers then push: "Now it's 500 million players, or 200,000 updates a second." Two ways to split it:
By score range
- Shard 1 holds scores 0–999, shard 2 holds 1,000–4,999…
- Top N reads only the highest shard
- Rank = players in all higher shards (a count each) + rank within the shard
- Players move between shards as they score, and the low shards are huge: very uneven
By player hash
- Even spread of players and writes
- Top N = take each shard's top N and merge (exact)
- Exact rank = sum over shards of "how many have a higher score" (
ZCOUNT): one call per shard, in parallel - Fine for tens of shards; slow if you have hundreds
The bigger insight: most players don't need an exact rank. Rank 31,402,117 and rank 31,402,118 mean the same thing to a human. A common design:
- Exact for the top 10,000 (they care, and the top shard is small)
- Approximate for everyone else: keep a histogram of scores in, say, 1,000 buckets, updated as scores change, and answer "you're in the top 38%" or "about rank 19 million" from it in O(1)
This also makes friends-only and per-country boards cheap: friends' boards are small sets computed on demand (fetch 200 friends' scores and sort), and per-country boards are just more sorted-set keys.
Phase 6: Trade-offs and failure modes (5 minutes)#
Redis primary dies
A replica is promoted within seconds. Updates acknowledged by the old primary but not yet replicated are lost from the sorted set, but not from Kafka or PostgreSQL: replay the last few minutes of events (idempotency makes replays safe).
Redis loses everything
The sorted set is derived data. Rebuild it from PostgreSQL season totals (50M ZADDs in batches: minutes), then replay Kafka from the rebuild's timestamp.
One very hot key
The whole leaderboard is one key on one node: by definition a hot key. Cache the top 10 for a second in the leaderboard service, and serve "my rank" reads from replicas, accepting a few hundred milliseconds of lag.
Writer falls behind
At the peak, consumer lag grows and ranks get stale. Scale writers by partition (player-keyed Kafka partitions keep per-player order), and show "updating…" rather than a wrong rank.
Cheating
Scores only come from game servers; the score service rejects impossible values; suspicious jumps go to review before they reach the top 100.
| Decision | We chose | We gave up |
|---|---|---|
| Rank structure | Redis sorted set (skip list) | SQL as the place ranks are computed |
| Truth | Kafka events + PostgreSQL totals | A single store for everything |
| Duplicates | Dedupe by match_id in a Lua script | Simpler at-least-once counting |
| Long tail | Approximate ranks from a histogram | Exact ranks for everyone, cheaply |
Go deeper in the course#
- Week 1, Day 4: Hot keys and skew: a single leaderboard key is the textbook hot key, and how to spread its reads
- Week 1, Day 1: Partitioning deep-dive: range vs hash partitioning, the trade-off behind sharding the board
- Week 2, Day 2: Idempotency in practice: idempotency keys and deduplication windows, the match_id pattern
- Week 3, Day 1: Queue vs stream: why score events go to a partitioned log that can be replayed
- Week 4, Day 4: Feed caching: sorted sets as a serving structure for ranked lists
- Week 1, Day 2: Replication trade-offs: reading ranks from replicas, and what lag means for them
- Week 8, Day 2: Streaming vs batch: its practice problem is a real-time leaderboard, with approximation for the global board
- Week 5 capstone: multiplayer game matchmaking: the game platform around the leaderboard, and why the leaderboard can be eventually consistent
Check yourself#
Leaderboard
Q1/4 Why is SELECT COUNT(*) WHERE score > mine a poor way to get a mid-table player's rank?
Answer & explanation
Answer: B
Rank is a count of everyone above you. With an index it's still O(rank). A sorted set's skip list finds a rank in O(log N).
Q2/4 A game server times out and resends the same match. How does the design avoid double points?
Answer & explanation
Answer: C
SET … NX succeeds only the first time, and both steps run atomically on one node, so a redelivered event changes nothing.
Q3/4 500M players, sharded by player hash. How do you get the exact global top 10?
Answer & explanation
Answer: D
Every member of the global top 10 must be in its own shard's top 10, so merging K small lists is exact.
Q4/4 Redis loses the whole sorted set. What makes recovery possible?
Answer & explanation
Answer: A
The sorted set is a fast index over durable data. Truth lives in the event log and the database.
New lessons by email
New lessons, side quests and case files as they ship, at most once a week. No spam, unsubscribe anytime.
Discussion · …
Public: everyone reading this lesson sees comments. Be kind and keep it on the material.