Design a Real-Time Leaderboard

A leaderboard ranks players by score and answers two questions quickly: "who are the top N?" and "what is my rank?" It sounds like a sorted list, and the interview value is in what happens at scale: millions of players, a flood of score updates, time-based windows (daily, weekly, all-time), ties, and the surprisingly hard question of computing an exact rank for any one player.

The chapter follows the usual shape: understand the problem, set up the interface, build the high-level design, then go deep on the questions interviewers use to separate levels.

1. Understanding the problem

Players earn scores in a game or a contest. The system shows a global leaderboard, a player's own rank and nearby players, and possibly boards for friends, regions and time periods.

Functional requirements

Core:

1. Record a player's score and update the ranking.
2. Show the top N players.
3. Show a given player's rank and the players around them.

Confirm in or out: multiple boards (daily, weekly, all-time), friends-only boards, per-region boards, how ties are broken, whether a score can go down, anti-cheat, and history. A sensible opening: "I will design a global all-time and a time-windowed board, with top N, my rank and neighbours, and treat friends boards and anti-cheat as extensions."

Non-functional requirements

• Low latency reads. Top N and my rank must return in milliseconds, since players check constantly.
• Near-real-time updates. A new score should show within a second or two.
• Scale. Assume 100 million players, 10 million active in a day, and bursts after events.
• Correctness of rank. Ranks must be consistent: no two players share a rank unless tied by the defined rule.
• Durability. Scores are the record and must not be lost, even though the ranked view can be rebuilt.

Estimation

What the numbers say. The data is small: a few gigabytes fit in the memory of a single machine. The rates are moderate. The hard part is not volume but providing rank queries quickly and correctly, and staying correct when a single node is no longer enough.

2. The set up

Core entities

• Player and score record: player identifier, score, time of last change.
• Board: a scope (global, region, friends) and a period (all-time, daily, weekly).
• Rank entry: player, score, rank.

API

POST /v1/scores              { player_id, delta | value, event_id }   -> ok
GET  /v1/boards/{board}/top?n=100                                      -> [{ player, score, rank }, ...]
GET  /v1/boards/{board}/players/{id}                                   -> { score, rank }
GET  /v1/boards/{board}/players/{id}/around?k=5                        -> neighbours above and below

The event_id makes the update idempotent, so a retried score submission does not count twice.

3. The naive approach and why it fails

A relational table scores(player_id, score) with an index on score gives:

• Top N: ORDER BY score DESC LIMIT N. With an index, this is fast.
• Update: change one row and the index. Fast.
• My rank: SELECT COUNT(*) FROM scores WHERE score > :my_score. This counts every row above you, which for a middling player in a 100-million-row table means scanning tens of millions of index entries. Done for every player check, it will not meet a millisecond target.

So the missing piece is a structure that answers "how many items have a higher score" in logarithmic time.

4. A structure built for ranking: the sorted set

An ordered structure keyed by score, such as a skip list or a balanced tree augmented with subtree sizes, supports in $O(\log n)$:

• insert or update a member's score,
• get the top N (walk from the end),
• get the rank of a member (count elements before it),
• get a range of members by rank, which gives the neighbours around a player.

In-memory stores provide this as a built-in sorted set type, mapping each member to a score and keeping them ordered. With 100 million members the memory is a few gigabytes, which fits in one machine's RAM. This is the standard first answer, and it makes the single-node design simple.

• The score service validates and deduplicates updates, then updates the sorted store and publishes a score event.
• The sorted store holds the live ranking and answers top N, rank and neighbour queries.
• The scores database is the durable record, written asynchronously from the event log. If the sorted store is lost, rebuild it from the database.

Tie-breaking. Decide the rule: equal scores rank by who reached the score first. Implement it by encoding the tie-breaker into the sort key, for example a composite score where the fractional part encodes the time, so that the structure orders them consistently, and every query gives the same answer.

5. Potential deep dives

Deep dive 1: Why not just compute rank with a query?

Weak: count rows with a higher score on demand. It scales linearly with the table, and a heavily used board spends all its time counting.

Solid: a sorted structure with $O(\log n)$ rank. Maintain the ranking in memory so that rank and top N are logarithmic.

Excellent: understand the cost model and choose by need. Exact rank for every player in real time needs an order-statistic structure. If the product only needs "top 100" and "you are in the top 10 percent", cheaper approximations exist: a histogram of score buckets gives an approximate percentile in constant time, and exact rank can be computed only for the top few thousand while others see "rank about 1,234,000" or a percentile. Say that exactness has a cost, and ask whether the product needs it for players deep in the ranking, who often care more about trend than the precise number.

Deep dive 2: Time windows (daily, weekly, all-time)

The challenge. Players want a daily board that resets, a weekly one, and an all-time one.

Weak: one board, filtered by timestamp at query time. Filtering a huge board by time on every read repeats expensive work.

Solid: a separate sorted set per period. Keep one structure per window, named by the window (daily:2026-10-02, weekly:2026-W40, alltime). A score update writes to each applicable structure. A new day simply begins writing to a new structure, and old ones expire or are archived.

Excellent: manage the lifecycle and the cost. Writing every score to three or four structures multiplies write cost, so update them from the same event, in one pipelined step, or compute the longer windows from the shorter ones periodically. Set a time to live on old boards so memory is reclaimed, and snapshot final results to durable storage at the end of each window for history and rewards. Handle the boundary: a score arriving just after midnight belongs to the next day, so use event time, not processing time, and decide how late events are treated. Pre-create the next window's structure before the boundary to avoid a spike at rollover.

Deep dive 3: Scaling beyond one node

The challenge. One machine's memory and throughput are limits. Eventually the board is too large, too busy, or needs higher availability.

First, check whether you need to. 3 GB of data and a few thousand operations per second fits a single well-provisioned node, with a replica for failover. Say so, because over-engineering is a common mistake. When you do need to scale, there are two main ways to split the data.

Option A: shard by score range. Each shard holds players in a score band. The exact rank of a player is the number of players in all higher shards (known from per-shard counts) plus the player's rank within their own shard. Top N reads the top shard (and the next if needed). Pros: exact ranks with little work. Cons: the top shard is the hottest, since most reads and many updates concentrate on high scorers, and players move between shards as scores change, so updates may cross shards. Shard boundaries need rebalancing as the score distribution shifts.

Option B: shard by player (hash of the identifier). Updates spread evenly. Top N is computed by asking every shard for its local top N and merging: the global top N is contained in the union of each shard's top N. Pros: even load and simple updates. Cons: exact rank for a given player needs the count of higher scores on every shard, so rank queries fan out to all shards. Use it when exact ranks matter only for the top of the board, and approximate ranks suffice elsewhere.

Excellent: pick by what is read most. If most queries are top N, shard by player and merge, caching the merged top N for a second. If exact ranks are needed everywhere, shard by score range with careful rebalancing, or maintain a global histogram of counts per score bucket that each shard updates, giving fast approximate ranks and an exact refinement within a bucket. Replicate each shard for availability, and keep the durable store as the source of truth.

Deep dive 4: Handling bursts and hot spots

The challenge. At the end of a tournament, millions of scores arrive within minutes, and everyone refreshes the leaderboard.

Weak: every refresh hits the sorted store. The read load can exceed what one node serves.

Solid: cache the top N, and absorb writes with a queue. The top 100 is the same for everyone, so cache it for a short time at the application layer and the edge. Buffer score updates through a queue so spikes do not overload the store.

Excellent: separate hot reads from personal reads, and batch writes. Serve the global top N from a cache refreshed every second or so by a single worker, since it is identical for all viewers. Personal queries (my rank, my neighbours) go to the store, and can use a short-lived cache keyed by player. Batch score updates and apply them in pipelined groups. For the top shard's write hot spot, partition the top range further or accept coalescing: if a player's score changes ten times in a second, apply only the latest. Rate limit abusive clients.

Deep dive 5: Consistency, idempotency and durability

Idempotent updates. Each score event has a unique identifier, and the service records processed identifiers (or uses upsert semantics for "set score" operations), so retries do not double count. Prefer "set to this value" or carry the event id for "add this delta".

Durability. The sorted store is in memory. Persist the stream of score events durably before or alongside updating it, and write the authoritative score to the database. If the store crashes, rebuild it from the database snapshot plus the events since. Keep a replica to avoid long rebuilds.

Ordering. For one player, apply updates in order, by partitioning the event stream by player identifier, so an old update cannot overwrite a newer one. Attach a version or timestamp, and reject stale updates.

Consistency of the board. A read may see a score change slightly late, which is acceptable. What must hold is that the ranking at any instant is consistent with a real set of scores, never a mix that gives two players the same rank or skips ranks.

Deep dive 6: Friends and regional boards

Regional boards are separate sorted structures per region, updated alongside the global one.

Friends boards are different: each player has a unique set of friends, so a structure per player would be enormous. Instead, at read time, fetch the scores of the player's friends (a batch lookup by identifier) and sort them in the application. A user with a few hundred friends makes this cheap. For users with huge friend lists, cap the list or sample.

Deep dive 7: Anti-cheat and trust

Leaderboards attract cheating. Do not trust the client to report scores. Compute or validate the score on the game server, sign submissions, rate limit, flag statistical outliers for review and keep a way to remove a player and recompute the ranking. Say this is both a technical and a policy matter.

6. What is expected at each level

Mid-level. You see that sorting a database table works for top N, notice that counting for rank is slow, and propose an in-memory sorted structure. You mention caching the top N.

Senior. You explain why a sorted set gives logarithmic rank, design time windows with separate structures and expiry, handle tie-breaking and idempotency, and describe the two sharding strategies with their trade-offs. You size the data and show a single node suffices at first.

Staff. You question the need for exact rank, use histograms or percentiles where appropriate, design rebuild and failover, manage burst traffic and hot shards, think about anti-cheat and fairness, and describe how the board evolves with the product.

7. Interview questions and model answers

Q: Why not use a database for the leaderboard? Top N is fine with an index, but a player's rank requires counting everything above them, which scans a huge range. An in-memory sorted structure gives top N, rank and neighbours in logarithmic time.

Q: How does a sorted set compute rank quickly? It is an ordered structure augmented with counts, so the number of elements before a member is found by walking the structure in logarithmic time, instead of scanning.

Q: How do you do daily and weekly boards? A separate sorted set per window, written from the same score event, with a time to live so old ones expire, and final results snapshotted to durable storage.

Q: How do you scale beyond one machine? Shard by score range for exact ranks, at the cost of a hot top shard and rebalancing, or shard by player and merge each shard's top N, at the cost of fanning out for exact ranks. I choose by whether top N or exact rank dominates.

Q: How do you break ties? Encode a secondary key, such as the time the score was reached, into the sort key so that the order is deterministic.

Q: What if the store loses its data? It is derived. Rebuild it from the durable scores database plus the event log, and keep a replica for fast failover.

8. Common mistakes

• Computing rank with a count query over a large table.
• Over-engineering with sharding when one node fits the data.
• A single board filtered by time on each read, instead of a structure per window.
• No tie-breaking rule, so ranks flicker.
• Non-idempotent score updates, so retries inflate scores.
• Sending every refresh of the top N to the store.
• Trusting scores reported by the client.