Design a leaderboard with exact snapshot ranks

01Problem and scope

A leaderboard maintains scores and answers ordered-list and rank queries. Define ties before selecting the index: competition rank equals one plus the number of players with strictly higher scores. Scores P1=920, P2=900, P3=900 and P4=880 produce ranks 1,2,2,4; a display tie-breaker does not alter shared rank. This design uses cumulative trusted results, authorized corrections and exact rank within a named complete published snapshot.

On one server, store each player’s score and sort the four rows. Order ties by player ID for display, but do not let that tie-breaker change their shared competition rank.

I ask, “Is the score a best attempt, a sum of match results, or a value that can be corrected downward?” We choose cumulative scores from trusted match results, including authorized corrections. I then ask whether the displayed rank must reflect a globally instantaneous state. We choose exact rank within a named complete published snapshot, normally no more than one second old. This is a more precise promise than an unspecified live rank.

A projection is a derived view built for a particular query. Here the score history records what was awarded, while an ordered projection arranges the current totals for top-list and rank queries. Rebuilding that view should reproduce the accepted scoring evidence rather than invent a second source of truth.

The score history and displayed index must agree about which updates they include. Event E77 awards P2 thirty points for match M91, moving the total from 900 to 930, above P1 at 920. If the event is delivered twice or arrives after a later correction, the board must remain explainable. The authoritative score history and the ordered display index are therefore separate parts of the design.

02Functional requirements

1. Submit a match result. Stable event identity returns one accepted score transition.
2. Read top 100. Complete ordered list and snapshot generation.
3. Read a player's rank. For a player such as P2, return one plus the number strictly above that player's score in the selected generation.
4. Read nearby players. Bounded neighbors under score then player-ID display order.
5. Correct a result. Audited new event may decrease a total.
6. Close a season. Publish a reproducible award snapshot, such as season S4, under the announced cutoff policy.

Scope and acceptance boundaries

Support top 100, a player’s rank, nearby competitors, seasonal/region scopes, and optionally friend-only boards. Assume one-second normal score visibility. Scores come from trusted game-result services, not raw browser claims. Best-attempt scoring, cumulative scoring, and decreasing scores are different rules; this exercise uses cumulative scores with explicit corrections.

Season closure needs an event cutoff, allowed-lateness policy, and immutable award snapshot/version. Creating a new season namespace is simpler than synchronously zeroing every old entry. Anti-cheat model training and matchmaking are separate products, but validation and audit trails belong on the scoring path.

Top 100 means one hundred display positions, not everyone tied at the hundredth score. If the product wants every boundary tie, the response can be much larger and needs a different pagination contract. Shared competition rank remains independent of that display truncation.

Friend-only boards are optional. If enabled, authorization filters the candidate population before rank and top-k calculation. Showing public global results and then removing nonfriends would not produce the correct friend leaderboard.

03Non-functional requirements

1. Workload. Assume 100 million participants, 10,000 score events/s, 50,000 top-list reads/s and 1,000 personalized rank reads/s.
2. Read latency. Target p95 below 100 ms for cached top lists and below 300 ms for an exact published-snapshot rank.
3. Availability and freshness. Target 99.9% monthly read availability and one-second normal score-to-snapshot visibility. These are exercise assumptions.
4. Durability and event application. Accepted score events survive one zone failure. Apply each logical event once and increase that player’s version with every accepted change.
5. Complete snapshots. Every ranking response names its generation. It may lag, but cannot mix shard generations or omit a failed shard while claiming completeness.
6. Final awards. Announce the event-time cutoff and allowed lateness, verify that all required input arrived, then preserve the final generation unchanged.

Snapshot exactness versus global real time

Generation s17 records a boundary vector: a list specifying the last committed input position included from each partition. It is reproducible but need not contain every event accepted before one wall-clock instant across unrelated owners. A stronger global real-time requirement needs a global cutoff/ordering protocol and its coordination cost.

Later adjudication

Fraud findings after a season award produce a separately audited adjudication version. They do not silently rewrite the historical award snapshot.

04Capacity estimates

These are exercise assumptions. A popular board can be hot even when individual player updates distribute evenly. Cache top lists briefly, but expose the snapshot generation and input positions they include, and ensure season/scope is part of every cache key.

At 1,000 exact rank reads/s across 100 player-hash shards, naive execution issues 100,000 count queries/s. Even small requests use network and index CPU; the slowest replies can delay the whole answer. Cache repeated player/generation ranks and batch counts for a generation, but do not confuse an approximate histogram answer with an exact rank.

The top list is highly shared: 50,000 reads/s over one popular board can reuse the same generation's 100 rows. A one-second snapshot cache reduces repeated 10,000-candidate merges to roughly one merge per board per generation rather than one per user request. Cache identity includes season, region, scoring rule, and generation.

If 100 million active player records occupy an illustrative 128 bytes in an ordered index plus lookup structure, the working set is 12.8 GB before allocator overhead, snapshots, and replicas. Three copies make 38.4 GB before those additions. Keeping five separate full snapshots multiplies storage. Copy-on-write or immutable versioned pages let snapshots share unchanged index pages. Benchmark the real engine's update and snapshot overhead before adopting the 128-byte assumption.

05APIs and contracts

Submit a score event

POST /score-events accepts {eventId:"E77",playerId:"P2",season:"S4",matchId:"M91",sourceRevision:1,awardedPoints:30,ruleVersion:2} from a trusted result service. It returns the saved event result and player score version, such as total 930/version 12. The event identity is scoped to season and player so it shares that player’s authority. The same identity and content return the same result; conflicting content under that scoped identity returns a conflict. A trusted source must also identify the canonical match contribution and its revision, so a second transport event ID cannot award the same match twice. The request carries the complete match contribution; the owner derives its delta from the stored contribution. Here a new match changes 0 to 30 points, so the player total increases by 30. Browser score submissions are not accepted directly.

Read top lists and rank

GET /boards/S4/top?limit=100&generation=s17 returns display order, competition ranks, generation, source-boundary metadata, and completeness. Omitting generation chooses the latest complete published generation. GET /boards/S4/players/P2?view=rank&generation=s17 uses player P2's score from s17, not the newer authoritative total, to avoid comparing values from different states.

Page within one generation

Nearby results carry a cursor over (generation,score,playerId,direction). Every page uses that cursor’s generation; switching generations as scores change could duplicate or skip neighbors. A generation older than the retained query window returns an explicit expiration response with a new starting generation.

Correct results and close seasons

Corrections identify the original match or adjudication case and their own unique correction event. Season-closed submissions return either a declared late-review status or rejection, rather than entering a supposedly final award board invisibly.

06Data model and access patterns

A player version orders changes to one player’s total; a board generation identifies one complete published view of many players. We need both because accepting P2’s new score does not instantly rebuild every shard’s ranking index. These records connect an accepted score to the later board snapshot that displays it.

Hashing player ID assigns its complete total and event deduplication record to one owner. The owner transaction records E77, adds thirty, assigns version 12, and creates an outbox record. Index workers receive the complete total and its version, rather than a bare instruction to add points. Thus a delayed version 11 update cannot overwrite version 12 or add thirty again.

The ordered index and player lookup must expose the same generation. Querying a fresh lookup with an older sorted structure could rank player P2's 930 against a population that still contains the old 900. The manifest names index snapshots that include both structures at each shard boundary.

Scoring-rule version belongs in the board namespace or rebuild metadata. Changing how a match awards points may require replay into a new board generation rather than applying new rules to only future players without disclosure.

07Basic working design

A hash map answers “what is player P2’s score?” but not “who is above player P2?” Maintain a score-ordered structure as scores change. A balanced ordered index supports efficient insertion and ranges; Redis sorted sets are one implementation option. Redis sorted sets.

For a small board, one database transaction records the match event and updates the player's total, then the application updates a local ordered projection. A versioned outbox makes that second step recoverable. The top list reads the highest scores, while a player lookup gives the score needed for the strict-greater count.

For the four-player example, an index snapshot initially stores player P1 920, player P2 900, player P3 900, player P4 880. Player P2 and player P3 each count one strictly greater score and return rank 2. After E77, a new complete snapshot stores player P2 930, player P1 920, player P3 900, player P4 880. Player P2 now counts zero greater scores and returns rank 1. The display tie-breaker only orders equal-scoring names.

The baseline can freeze a short-lived immutable snapshot for pagination and award calculation. It does not need 100 shards merely because a leaderboard could become large. The single index is easier to reason about and serves top, rank, and neighbors without a distributed fanout. We add distribution only after its measured capacity or ownership becomes a constraint.

Choose the tie comparator explicitly at the API boundary. Redis reverse score ranges reverse lexicographic order for equal-score members too; if the product chooses player-ID ascending ties, a plain reverse range is not that comparator. Adapt the representation/query deliberately and test boundary ties. A mutable Redis sorted set also does not supply retained historical query generations by itself. Freeze a complete copy at small scale or use a versioned ordered index with snapshot retention; Redis persistence files are not a pagination snapshot API.

08Find the baseline flaws

If E77 is applied as an unguarded increment, one network retry moves player P2 900→930→960. A sorted set faithfully ranks the incorrect total; the index did not cause the bug. The scoring authority must record event identity and the total change atomically before an index can be trusted.

At scale, 50,000 top-list reads/s can consume about 240 MB/s of response payload for one board. Sending every request through the scoring database competes with score updates unnecessarily. A shared immutable-generation cache handles this repeated result cheaply.

Sharding introduces a less obvious error. Suppose the query reads player P2's new score 930 from shard A but shard B still reports an old snapshot in which player P1's score is 920 rather than a newly accepted 950. The response says player P2 rank 1 even though its claimed latest state is incoherent. To give an exact answer, the response must use the shard snapshots listed in one shared manifest.

A hot board's global rank query also touches every shard. Even if each count takes only a few milliseconds, the slowest shard sets response latency and a missing shard prevents an exact complete result. Hashing players spreads updates across shards, but global rank still needs results from all of them.

09Improve the design, step by step

First, separate authoritative scoring from the index. The trigger is duplicate event delivery and rebuild needs. A local score transaction stores event identity, new total/version, and an outbox update. Projection workers conditionally apply only newer versions. Saved events support recovery and audits, but the display can lag while outbox updates are processed. A single transactional ordered database remains simpler at small scale if it can serve both roles safely.

Second, cache complete top-list generations. The trigger is repeated hot-board reads. A builder computes top 100 once per published generation and the serving tier caches that immutable result. This reduces merge work and network pressure on index owners, but displays a bounded older result and requires generation-aware cache keys. Live per-request index reads are preferable for small boards whose freshness requirement is stricter than the snapshot interval.

Third, shard complete player totals. The trigger is one ordered index's write or memory limit. Hash each player to an owner, maintain a local ordered index, and merge local top-k candidates. This distributes updates while preserving the top-k proof. Exact global ranks now query every shard, and snapshots must be coordinated. Score-range partitions can reduce some rank aggregation but introduce score movement and hot ranges; choose them only after measuring those tradeoffs.

Fourth, publish coordinated index generations. The trigger is inconsistent cross-shard reads and reproducible awards. The manifest builder chooses the last committed input position to include from each partition. Each shard builds and retains a snapshot through its assigned position. Only after every required shard reports readiness does the builder publish s17. Queries pin s17. This adds snapshot storage, build latency, and a slow-shard dependency. Uncoordinated live counts remain acceptable only if the product labels their answer as an estimate rather than exact rank.

A missing shard delays publication while the prior complete generation remains readable. That turns a partial failure into explicitly stale data, preserving the meaning of a rank instead of secretly dropping competitors.

10Detailed architecture

Partitioning and exact query proofs

For player P2’s exact global competition rank, each shard counts scores strictly above the selected score at the same snapshot; sum and add one. Uncoordinated live counts from different moments give a moving estimate, not a precise instantaneous rank. For nearby competitors, fetch bounded candidates above and below the player and merge them using the same ordering. For friend-only results, filter before cutting off the candidate list, or fetch more afterward.

Score acceptance and publication

Authenticate each match result, then route it to that player’s score owner. Its replicated store keeps events, totals, versions and outbox records. Projection workers build shard-local lookup and ordered structures. A generation coordinator publishes a board manifest only after all shard snapshots meet its recorded boundaries.

Query scope and caching

The query aggregator obtains the latest complete manifest, routes parallel requests to its index snapshots, and merges the returned candidates or counts. A top-list cache stores immutable generation results. For private or friend-only responses, check access before shortening the candidate list and include that access scope in the cache key.

Recovery and completeness

The archive stores score events and checkpoints for rebuild, while season finalization consumes a verified complete generation and writes an immutable award snapshot. A score is accepted when the score-owner transaction commits. Workers then publish the outbox, update indexes, build generations and cache top lists. Queries are exact for their selected manifest; newer totals in the score database may not yet appear there.

Replicas help serve a given shard snapshot, but every required shard must still contribute to an exact global count. A query does not become complete by receiving ninety-nine of one hundred replies.

11Write path and acknowledgement

Before changing a total, validate the result and check whether it was already applied. Corrections also need revision checks so a late old correction cannot undo a newer one.

1. Validate M91’s signed authoritative result and scoring-rule version.

2. In one score-owner transaction, record E77 as processed and change player P2 900→930/version 12.

3. Emit the versioned total; the index ignores an equal/older version on replay.

4. At snapshot s17, the rank query counts zero players above 930 and returns rank 1.

5. A second delivery of E77 does not add thirty again; a correction is a new authorized event/version, even if its total decreases.

6. The outbox may deliver version 12 repeatedly. The projection transaction checks the stored player version and replaces the old ordered score and lookup together only when the incoming version is higher. Equal-version identical updates are no-ops; equal-version conflicting totals raise a consistency alarm.

7. The generation builder chooses a boundary vector that includes player P2's update on the owning shard. Each shard freezes the corresponding index state. Once all are ready, the board manifest publishes s17 atomically.

8. Top-list workers merge the local candidates for s17 and write an immutable cache entry. A lost cache write can be retried because the generation's answer is fixed.

9. An authorized correction later creates version 13, perhaps reducing player P2 to 905. It is a new event, not an attempt to overwrite E77's evidence. A later generation reflects the correction, and any already finalized award version follows the adjudication policy.

The acceptance reply can show player P2's authoritative total 930 before s17 is published. The UI labels the board as updating instead of mixing that fresh total into an older snapshot rank.

12Read and delivery path

Rank uses one complete snapshot and the agreed tie rule. A top list, nearby players and an arbitrary player’s exact rank require different work.

1. The API authenticates the reader and selects board S4, scope, and a complete manifest generation. A supplied generation pins the request; the default resolves once at the start.
2. Top 100 first checks its generation cache. On a miss, the aggregator requests each shard's local top 100 under score-descending/player-ID order and merges at most 10,000 candidates for 100 shards.
3. Player P2's rank first reads player P2's score from the lookup for that same generation. Each shard counts players with strictly greater scores. Sum the counts and add one. A player-ID tie-breaker does not enter the competition-rank count.
4. Nearby queries collect bounded predecessors and successors under the display order, merge them, and separately label shared ranks. A large tie group may require cursor pagination even though its members share a rank.
5. If one shard cannot serve the requested snapshot, the service either serves a different explicitly identified complete generation, marks an approximate response as such, or fails the exact request. It never reports a partial count as a complete rank.
6. The response includes generation age and boundary metadata. A client can keep pagination stable while refreshing to a newer generation deliberately.

For friend-only top lists, filter to the authorized friend population before truncating candidates, or continue fetching until enough eligible candidates are proven. Post-filtering a global top 100 can omit every relevant friend outside that list.

13Correctness deep dive

The score owner handles competing deliveries of E77 using one database transaction:

applyScore(E77, P2, M91, sourceRevision=1, awardedPoints=30):
  begin; lock score(S4,P2)
  if scoped event (S4,P2,E77) exists:
      verify identical fingerprint; return its saved result
  verify trusted match M91 and scoring rule 2
  require sourceRevision > stored match contribution revision
  delta = awardedPoints - stored awardedPoints  # 30 - 0 here
  insert unique scoped event E77 with immutable payload
  store match contribution (M91, sourceRevision=1, awardedPoints=30)
  update total by delta: 900 -> 930 and version 11 -> 12
  insert outbox(P2, version12, total930)
  commit

Workers A and B can both receive E77. If A commits first, B's unique event lookup returns the saved version 12 result. If A crashes before commit, B can apply the event once. If A crashes after commit but before replying, B still finds the durable result. The outbox ensures the score change cannot be permanently hidden merely because the process died before publishing it.

The global top-k proof requires complete player totals. If a player is absent from its shard's local top k, at least k players on that shard precede it under the same deterministic display order. Therefore it cannot belong to the global top k. Merging every local top k is sufficient. This proof would fail if each shard held only partial contributions to one player's score.

Correction order belongs to the match contribution, not just arrival order. Store the last accepted source revision and points for each player/match. A correction contains a complete new contribution and a higher source revision; the owner computes delta = new contribution minus stored contribution, then changes the match row, total, player version and outbox atomically. For E77, contribution 0→30 moves total 900→930. A later correction 30→5 subtracts 25 and gives 905. An older revision arriving afterward cannot subtract again or restore the obsolete award. Event IDs suppress transport repetition; match revision checks prevent different event IDs from repeating the same business result.

14Failure and recovery

15Operations, security, and cost

Suppose shard B fails during rank computation. Returning counts from only A makes player P2 look better than the complete result; mark the answer incomplete, serve an explicitly dated complete snapshot, or fail the exact-rank request. Short-lived cached top lists and replicated indexes help availability but do not remove this choice.

Authenticate score producers, audit corrections, protect private friend graphs, and bound query scopes. Monitor score-to-index lag, dedupe rate, score/index divergence, rank p99, hot-board load, rebuild position, and season-finalization completeness. Test tied scores, duplicate events, decreasing corrections, cross-shard reads, and a lost index. The saved game result is authoritative; the displayed ranking can be rebuilt from it.

Track authoritative event acceptance separately from score-to-generation visibility. A board can serve cached reads successfully while indexing is stalled. Alert on oldest unpublished score event, incomplete generation age, per-shard skew, and discrepancies between score checkpoints and ordered-index totals. Periodically compare sampled ranks with a slow offline sort of the same snapshot to detect incorrect ranking results.

A rule rollout replays a recorded match set into a new board namespace and compares expected differences before switching the manifest. A shard migration copies a checkpoint, replays through a declared boundary, and publishes a new routing/generation manifest; it does not move a player twice into one snapshot. Recovery drills include duplicate events, downward corrections, ties at the top 100 boundary, and shard failure during season finalization.

At 10,000 events/s and 100 bytes, raw history is 86.4 GB/day. Ninety days is 7.776 TB before replication and indexes. Immutable award snapshots occupy relatively little storage compared with raw match history. Retain the final rankings and the evidence needed to reproduce them; removing a top-list cache offers little saving against that history volume. The larger cost question is how many active scopes and historical index generations must remain immediately queryable.

16Decision ledger and limitations

Retain authoritative score events plus score-state checkpoints. If an ordered shard disappears, reconstruct its player totals and replay newer versions before serving a complete board. During season close, freeze a reproducible watermark; delayed results become permitted corrections or go to the next adjudication process. Do not silently change an already awarded snapshot.

For this workload, player hashing balances score writes and complete-total ownership makes top-k merging straightforward. The cost is querying every shard for rank and waiting for complete generations before publication. A score histogram could return approximate percentiles cheaply, but bucket counts cannot generally provide an exact rank inside a bucket without refinement.

Snapshot publication trades a small, explicit freshness delay for reproducible answers. A global linearizable current rank would require stronger cross-shard coordination and likely higher tail latency. That is a separate product choice, not an optimization hidden behind the same endpoint.

The limiting case is a very popular global board with many personalized exact-rank requests. Measure the shard queries, then consider precomputing ranks in batches, combining counts in a hierarchy, or offering an approximate mode. Each changes cost or semantics and should be exposed rather than calling all of them “real-time rank.”

17Interview closing

“I first define competition rank as one plus the number of players with a strictly greater score. Equal scores share a rank; a deterministic display tie-breaker does not change that rank. A trusted match event changes one authoritative player total in a transaction that also records its event identity and an outbox update. The ordered projection consumes complete versioned totals, so retries do not add points twice and a later correction can lower a score safely.

“At scale I hash complete players across owners, merge each shard's top 100, and compute exact rank by summing strict-greater counts. Every lookup and count is pinned to a complete published generation, normally within one second of scoring. That gives reproducible snapshot exactness, not a hidden promise of global real-time linearizability. Cached top lists absorb the shared read load.

“The costs are cross-shard rank fanout, snapshot retention, and a slow shard delaying freshness. I would monitor score-to-generation lag and rebuild correctness, then test duplicates, ties, downward corrections, and an unavailable shard during season awards. The next measurement is whether personalized rank traffic, rather than score updates, is the actual bottleneck.”

If the interviewer demands instant global rank for every update, I would discuss a single ordered authority or stronger coordinated reads and quantify their limits. If approximate percentile is enough, hierarchical histograms can reduce cost, with the approximation stated explicitly.

Recall the key ideas

Answer from memory before opening each card. Explain why the choice works and what it costs. Revisit missed cards tomorrow.