19. Real-Time Leaderboard Intermediate → Advanced + Sorted-Set-Driven Scale-Out

A. Realistic Interview Prompt

Every functional requirement in this problem is really the same underlying operation asked three different ways — "where does this score rank among everyone else's" — and the entire design lives or dies on picking a data structure that answers that question natively instead of computing it from scratch on every request. The central technical decision this design is built around is Redis sorted sets (ZSET) as the core structure for every score and rank operation — ZADD for an O(log N) score update, ZRANK/ZREVRANK for an O(log N) rank lookup, and ZRANGE/ZREVRANGE for an O(log N + M) top-N or neighborhood query — combined with sharding the leaderboard by natural segment (per-game, per-region, or per-time-window) the moment one ZSET or one node's throughput stops being enough, accepting an approximate global rank across shards in exchange for that scale-out, and asynchronous write-behind persistence of scores to a durable store so Redis stays the fast path without quietly becoming the only copy of the data that exists. A candidate who reaches for a relational table and an ORDER BY here isn't missing a small optimization — they're missing the one decision the entire rest of the problem is downstream of.

B. Difficulty

Intermediate at the surface, advanced the moment sharding and write volume enter the conversation. The single-node version of this problem — one Redis sorted set, one game, tens of thousands of players — is a comparatively contained, well-known pattern that a candidate who has internalized Prerequisites D can produce quickly. What separates a mid-level answer from a senior one is everything that happens once a single ZSET stops being enough: sharding a data structure that was specifically chosen because it keeps everything in one globally-ordered set, and then having to reconstruct an approximate answer to a question ("what's my global rank") that no longer has one authoritative place to compute it. This problem rewards a candidate who can precisely say when the simple version breaks and what it costs to fix.

C. What This Problem Tests

• Whether a candidate reaches for a purpose-built ordered data structure (a sorted set, Prerequisites D) instead of a general-purpose relational table the moment "rank" and "top N" are the dominant access patterns — the single most consequential decision in this problem, made in J and K
• Real comfort with what a sorted set's skip-list backing actually buys — O(log N) inserts and O(log N + M) range/rank reads — versus reciting "Redis is fast" as an unexamined fact (M, N)
• Judgment about when a single node's capacity or throughput is genuinely exhausted, and the honest cost of what sharding gives up (an authoritative global rank) in exchange for scale-out (O) — directly building on the general sharding pattern named in Patterns Playbook G
• Recognizing that Redis's speed is precisely what makes it unsuitable as a system of record on its own, and designing a durability path that doesn't sacrifice that speed to get it (S)
• Distinguishing problems that look similar but are not: a leaderboard's need for near-real-time, individually-visible score updates versus Ad Click Aggregation's tolerance for batched, minutes-later aggregate counts (P16) — and being able to say precisely why the same batching trick doesn't transfer here (P)

D. Requirements

Functional

1. submitScore(player_id, score, segment) — record or update a player's score within a given leaderboard segment (e.g., a specific game mode, region, or time window)
2. getRank(player_id, segment) — return a player's current rank (1-indexed, highest score first) within a segment
3. getTopN(segment, n) — return the top N players and scores in a segment, ordered
4. getScoresAround(player_id, segment, range) — return the players immediately above and below a given player, e.g. "5 above, 5 below" — the "where do I stand relative to my neighbors" view most players actually look at
5. Support multiple independent, concurrently-active leaderboard segments (per-game, per-region, and per-time-window, per R) from the same underlying system, not one hardcoded global list

Non-Functional

• Low-latency reads on the hot path — a rank or top-N query is a foreground, user-facing request (opening the leaderboard tab mid-game); illustratively sub-100ms at p99, not a value a player will tolerate waiting seconds for
• Near-real-time write visibility — a player's own score update should be reflected in their own next rank query essentially immediately (V), a materially different bar than "eventually, within minutes," which rules out the large-batch aggregation approach that a lower-stakes analytics problem could get away with (P16-O, revisited in P)
• High, bursty write throughput — score updates are not smooth traffic; they spike hard at predictable moments (a match ending, a raid boss going down simultaneously for thousands of players) and the design must absorb that spike without falling over or falling meaningfully behind
• Horizontal scalability past a single node's memory and throughput ceiling — the defining scale requirement once a segment's player count or write rate outgrows what one Redis instance can hold or serve (O)
• Durability of scores across a crash — a node restart or failure must not silently erase a meaningful amount of already-acknowledged score history, which rules out treating Redis as the sole system of record (S)

Out of scope: anti-cheat or score-validation logic beyond a basic sanity bound on submitted deltas (a real system would layer this on top, but it's a separate concern from the ranking structure itself); a social/friends-only leaderboard view (a straightforward filtered read on top of the same structure, not a new mechanism); active-active multi-region writes for the same segment (an explicit follow-up, AA); matchmaking or skill-rating computation (ELO/MMR) feeding the score itself.

E. Clarifying Questions

F. Assumptions

• Confirmed: multiple independent segments rather than one monolithic leaderboard (E's clarifying question 1) — illustratively, one segment per (game title, region), each with its own player population and its own capacity profile.
• Assumption: writes are genuinely bursty, not smooth (E's clarifying question 2) — a popular title's matches tend to end in loose clusters, and a live event can produce a synchronized spike across a huge fraction of concurrently active players at once; the design is sized and shaped around that peak, not the daily average (G, P).
• Assumption: an approximate global rank is acceptable the moment a segment is sharded (E's clarifying question 3) — exact-at-all-times global ordering across shards is achievable but expensive, and this design treats "close, fast, and continuously refreshed" as the right default, with an explicit fallback to a slower exact computation as a fallback path (O).
• Assumption: ties are broken deterministically by a secondary key — earliest timestamp at which the current score was reached — rather than left arbitrary (E's clarifying question 4), because "why is this player ranked above someone with the identical score" is a question players actually ask and a design should have a real, stable, explainable answer to (Q).
• Assumption: both an all-time leaderboard and rotating daily/weekly leaderboards are required, and a closed time window's leaderboard remains queryable (E's clarifying question 5), e.g. "yesterday's top 100" — this shapes R's rotation-and-archival strategy rather than a single, ever-growing structure.

G. Capacity Estimation

H. Core Entities

I. API Design

POST /segments/{segment_id}/scores   { player_id, score }  -> { rank, encoded_score }   -- ZADD under the hood (K)
GET  /segments/{segment_id}/rank/{player_id}                -> { rank, score }           -- ZREVRANK + ZSCORE (N)
GET  /segments/{segment_id}/top?n=100                        -> { entries: [ { player_id, score, rank } ] }  -- ZREVRANGE (M)
GET  /segments/{segment_id}/around/{player_id}?range=5       -> { entries: [ ... ] }      -- ZREVRANK then windowed ZREVRANGE (M)
-- internal, not client-facing:
(write-behind) score update → durable store, async, batched               (S)
(aggregation) per-shard summary refresh → global-rank aggregation tier    (O)

Why submitScore returns the caller's own rank directly, inline with the write: the single most common next action after a score update is "so where do I stand now" — returning it as part of the same response (computed off the same ZSET the write just landed in, per V's read-your-own-writes guarantee) saves the obvious, near-guaranteed follow-up request every client would otherwise make immediately.

Why around is a first-class endpoint rather than something clients reconstruct from top and rank: "players near me" is the view most players actually spend time looking at — the global top 10 is largely aspirational for the other 99.99% of players, but "who's just ahead of me" is directly actionable and the most frequently polled read in practice, so it earns its own efficient, purpose-built query path (M) instead of being an afterthought composed from two other calls.

J. Basic / Naive High-Level Design (V1)

The simplest thing that satisfies the functional requirement in isolation: a single relational table scores(player_id, segment_id, score), with an index on (segment_id, score). A score update is an UPDATE (or UPSERT) statement; the top N is SELECT * FROM scores WHERE segment_id = ? ORDER BY score DESC LIMIT N; a player's rank is computed with SELECT COUNT(*) FROM scores WHERE segment_id = ? AND score > ?.

V1 — every score update, top-N query, and rank query is a direct SQL statement against one indexed table. Correct in isolation, and the rank query in particular gets strictly, measurably worse as the segment grows, not just slower in a vague sense.

Why this breaks specifically on the rank query, not just "under load" in general: SELECT COUNT(*) WHERE score > ? is, even with a perfect B-tree index on score, fundamentally an operation whose cost scales with how many rows satisfy the predicate — for a player near the top of a 20M-player segment, that's a scan (or index range-count) touching a large fraction of the entire table, on every single rank request, from every single player, continuously, as scores keep changing underneath it. This isn't a query that's merely unoptimized; it's a query asking a b-tree index to answer a question — "what is this row's ordinal position among all rows" — that a b-tree index doesn't natively track at all, unlike a structure built specifically to keep that ordinal position current as a first-class property of every insert (K). The ORDER BY ... LIMIT N top-N query fares comparatively better (the index can satisfy it without a full scan), but it still re-sorts against live, constantly-mutating rows on every call rather than reading from a structure that stays sorted by construction — and under a write-heavy score-update workload, index contention on the same hot (segment_id, score) index that every rank query is also hammering compounds both problems together.

K. Data Model & Store Choice

The core structure: a Redis sorted set (ZSET) per leaderboard segment, member → score. Each player_id is a member of the segment's ZSET; its score is the ZSET's native numeric score. This single choice is what turns every operation in D into a native, purpose-built command instead of a query pattern the underlying store has to work against: ZADD for a score update, ZREVRANK for a rank lookup, and ZREVRANGE for a top-N or neighborhood read — each one backed by the sorted set's internal skip list plus hash table (Prerequisites D; deepened for a general caching structure in P11, applied here as the primary data structure rather than a supporting cache), which keeps every member in sorted order as a standing property of the structure itself, continuously, rather than something recomputed per query.

Why a hash (player_id → score) alone doesn't work, even though it would satisfy the write path fine: a plain hash gives O(1) score lookup by player, but has no notion of order at all — computing a rank or a top-N from a hash means reading out every member and sorting them, on every request, which is exactly the "recompute order from scratch each time" cost this design exists to avoid. The sorted set is the one structure that gives both O(1)-ish membership semantics and maintained order simultaneously.

Score encoding carries more than just the raw score: the ZSET's score field stores a single 64-bit double, but that double is engineered to encode both the player's actual score and a tiebreak component in one sortable number (Q) — the sorted set only ever sorts on this one field, so anything that needs to influence ordering has to live inside it.

Redis's single-threaded command execution (Prerequisites D) is what makes a read-modify-rank sequence safe without extra locking: a ZADD updating a score and a concurrent ZREVRANK reading a different player's rank against the same set can never interleave in a way that produces a torn or inconsistent read — the same atomicity guarantee that backs Rate Limiter's atomic check-and-increment applies here to every sorted-set command.

L. End-to-End Flow — A Player's Score Updates After a Match, and Another Player Queries Their Own Rank

Player A's write and its own rank read-back both hit the same in-memory ZSET synchronously and return in well under the latency budget (G); the durable write is queued and flushed asynchronously, entirely off that critical path. Player B's independent rank query, moments later, already reflects Player A's new score — no batching delay, no staleness window for anyone reading the same shard.

M. Deep Dive 1 — Sorted Set as the Core Data Structure

Requirement this addresses: low-latency reads and writes on the hot path (D) — the direct fix for V1's fundamental mismatch (J) between "how a relational index is built" and "what a leaderboard actually needs to ask of its data on every request."

What a sorted set actually is, underneath the command names: Redis implements a ZSET as a skip list paired with a hash table over the same members — the hash table gives O(1) lookup of a member's current score (used internally by commands like ZSCORE), and the skip list is what keeps every member reachable in sorted order without a full re-sort on each insert. A skip list is a linked structure with multiple "express lane" levels above the base list — inserting or searching can skip over large stretches of already-ordered elements by hopping between levels, the same logarithmic-search intuition a balanced tree gives, without a tree's rebalancing overhead on every mutation.

The two operation costs that matter, stated precisely (extending Prerequisites D's introduction of sorted sets, and cross-referencing P11's general treatment of Redis's own internal structures): ZADD — inserting or updating a member's score — is O(log N), because the skip list only has to walk down through its levels to find the correct sorted position; ZRANGE/ZREVRANGE — reading M consecutive members starting from a position — is O(log N + M), the log N to locate the starting position plus a cheap linear walk across the M members actually being returned. Neither cost grows with how many members are ahead of or behind the ones actually being touched — a top-10 query against a 20-million-member set pays for 10 elements, not 20 million.

Why this is the correct structure specifically for this problem's access pattern, not just "a fast key-value store would also work": every functional requirement in D — submit a score, get a rank, get a top N, get a neighborhood — is a query against the set's order, not against an individual key's value in isolation. A structure that has to reconstruct that order per query (a hash, a plain relational table without a maintained rank column) pays a cost that scales with the dataset size on every read; a structure that maintains order as a standing property pays that cost once, at write time, amortized across every read that follows — precisely the trade a read-heavy, order-dependent workload (G) should make.

N. Deep Dive 2 — Getting a Player's Rank Efficiently

Requirement this addresses: the rank-lookup functional requirement (D) — the single query V1's naive COUNT(*) WHERE score > ? approach (J) handles the worst of all.

Why ZREVRANK is a fundamentally different operation from a COUNT query, not just a faster implementation of the same idea: a rank is precisely "how many members are ordered ahead of this one" — and a skip list's very structure already tracks, at each level, how many elements are being skipped over by each express-lane hop. Walking down to a member's position while summing those per-hop skip-counts is computing its rank, as a natural byproduct of the same traversal that would locate it for any other purpose — there is no separate counting pass, no scan, and no dependency on how many rows happen to satisfy a predicate. This is why ZREVRANK is O(log N): it costs exactly what locating the member costs, full stop, regardless of whether that member is ranked 1st or 19,999,999th.

The direct, quantitative contrast with V1: for a 20M-member segment, COUNT(*) WHERE score > ? against a near-top player scans on the order of tens of millions of rows (or a large index range); ZREVRANK against the same player costs roughly 24 skip-list comparisons (log₂ 20,000,000), regardless of whether that player is ranked near the top or near the bottom of the set. The gap isn't a constant-factor speedup — it's the difference between a cost that scales with dataset size and one that scales with its logarithm, and that gap only widens as the segment grows.

What a rank query still cannot do cheaply, even with a sorted set: ZREVRANK answers "this member's position within this one ZSET" — it has no concept of a member's position relative to members that live in a different ZSET entirely. That limitation is exactly what O's sharding deep dive has to solve for once a segment's data no longer fits in one set.

O. Deep Dive 3 — Sharding the Leaderboard

Requirement this addresses: horizontal scalability past a single node's memory or throughput ceiling (D) — the point at which a segment's player count or write rate outgrows what M and N's single-ZSET model can serve from one Redis node, and the general "split the dataset across many machines" problem (Prerequisites C) meets a structure that was specifically chosen because it keeps everything globally ordered in one place.

Choosing the shard key — the natural ownership boundary, per Patterns Playbook G: the right shard key for a leaderboard is whatever boundary players are actually compared within — sharding a per-region leaderboard by region, or a per-game-mode leaderboard by mode, keeps the overwhelming majority of real queries (a player's rank within their own region) hitting exactly one shard, the same "match the shard key to the access pattern" reasoning Patterns Playbook G names generally for Ticketmaster's event_id and Job Scheduler's job_id. Only when even one such natural segment individually outgrows a single node — a truly massive single all-time global leaderboard, say — does the segment itself need to be split further, by a hash of player_id across N sub-shards.

The hard problem this creates: once a segment is split across shards, no single shard knows the true global rank of any player, because each shard only sees its own slice. ZREVRANK against shard 3 tells you a player's rank among shard 3's members only — it has no way to know how many players on shards 1, 2, and 4 also outrank them. A naive fix — query every shard on every rank request and merge the results exactly — works, but turns every single rank query into a fan-out across every shard, reintroducing exactly the kind of full-scan cost per request this whole design exists to avoid, just spread across a cluster instead of one table.

The chosen approach: an approximate global rank, computed from periodically-refreshed per-shard aggregates, not an exact cross-shard merge on every request. Each shard periodically publishes a lightweight ShardAggregate (H) — its member count and a coarse histogram of how many members fall into each score bucket. A separate, small aggregation tier merges these per-shard histograms into one approximate global score distribution, refreshed on a short interval (illustratively every few seconds). A player's approximate global rank is then computed cheaply: their exact local rank within their own shard (an O(log N) ZREVRANK, per N) plus an estimate of how many higher-scoring players exist on every other shard, read directly off that shard's histogram bucket counts — no other shard needs to be queried live, at the cost of that estimate being a close approximation rather than an exact count.

Why this trade is the right one for this problem specifically, and not a corner cut for convenience: a player's own rank moving from "exactly 88,912th" to "approximately 88,900th-88,930th" is imperceptible and inconsequential to that player, while an exact cross-shard merge on every rank query would mean every single request pays a fan-out cost proportional to the shard count, permanently, for every player, forever — a cost paid on the hot, foreground read path (D) to buy a precision nobody asked for and no one can actually perceive. The one place exactness still matters — the literal top N global leaderboard, which is small and displayed prominently — is handled separately and cheaply: since top-N results only ever come from the highest-scoring end of each shard, merging each shard's own top N (a small, bounded ZREVRANGE per shard) into one final sorted list is exact, cheap, and only has to happen for the handful of requests actually asking for the global top N, not for every rank lookup.

P. Deep Dive 4 — Real-Time Score Updates at High Write Volume

Requirement this addresses: high, bursty write throughput (D) without sacrificing near-real-time visibility (D) — the two requirements this deep dive has to satisfy simultaneously, because the obvious fix for one (batch writes to smooth out a spike) directly threatens the other.

Why a single wildly popular event can genuinely threaten a single shard, even though the ZSET operation itself is cheap: a raid boss defeated simultaneously by thousands of players, or a match server flushing a whole lobby's final scores at once, produces a synchronized burst of ZADD calls against the very same segment's ZSET at the very same instant — the per-command cost (M) stays O(log N) throughout, but the aggregate request rate against one Redis node's single command-execution thread (Prerequisites D) can still exceed what that one thread can serialize through, queueing requests and adding latency exactly when the leaderboard is being watched the most.

The direct, explicit contrast with Ad Click Aggregation's async-batch approach (P16-O) — and why that trick doesn't transfer here: P16's windowed aggregation deliberately buffers events for up to a minute before finalizing a rollup, because a dashboard showing "clicks in the last minute" a minute late is an acceptable, even unnoticed, staleness. A leaderboard cannot borrow that same trade: the entire point of a real-time leaderboard is that a player who just won a match sees their new rank now, not sixty seconds from now — the near-real-time-visibility NFR (D) is precisely the requirement P16 doesn't have to carry. So the fix here has to smooth request volume without introducing score visibility delay — a materially narrower target than P16's.

The actual fix: client-side and edge-side write coalescing on redundant intermediate updates, not delaying final ones. A single player's score frequently changes several times in rapid succession during active play (successive kills, successive checkpoints) before that player actually cares about seeing an updated rank — the application layer coalesces a burst of intermediate updates for the same player into a single ZADD carrying only the latest value, dropping the superseded intermediate ones entirely rather than writing (and rank-checking) after every single one. This is meaningfully different from P16's windowing: nothing here waits on a clock before becoming visible; multiple updates to one player collapse into one, and the one write that does happen lands and is immediately visible, with no player-perceptible delay at all.

Where genuine backpressure is still needed, distinct from coalescing: for a true synchronized-burst event (an entire lobby's final scores landing within the same second), the write path fans out across all of a segment's shards (O) rather than funneling through one, and a small, bounded write queue absorbs the peak of the burst with a queueing delay measured in low milliseconds — not the seconds-to-minutes P16's batching windows tolerate — before draining into the ZSETs, keeping the "visible almost immediately" guarantee intact even under a real spike.

Q. Deep Dive 5 — Handling Ties

Requirement this addresses: a deterministic, explainable rank for every player (D, E's clarifying question 4) — a gap a plain sorted set leaves genuinely open on its own.

Why a ZSET alone doesn't resolve a tie the way a candidate might assume: when two members share the identical score, Redis's own tie-break for range and rank ordering falls back to lexicographic comparison of the member strings themselves — which means two tied players' relative order is effectively determined by how their player IDs happen to compare as strings, with no relationship whatsoever to anything meaningful about how or when they actually achieved that score. That's a real, deterministic, reproducible ordering — but not one any product requirement asked for, and not one that survives a "why is he ranked above me, we have the same score" support ticket.

The fix: encode the tiebreak directly into the score itself, so the ZSET's one native sort key does all the work. Rather than storing the raw score alone, the stored value is a single composite double engineered so that its primary digits are the actual score and its remaining precision encodes an inverted timestamp of when that score was achieved (inverted so that earlier achievement sorts as the "better" tiebreak, rewarding whoever got there first) — e.g. encoded = score + (1 - normalized_achieved_at_fraction) × epsilon, chosen so the timestamp component can never be large enough to change which whole-number score bucket a member falls into, only the ordering within a tied bucket. Every ZSET operation (M, N) then continues to work completely unmodified — ZADD, ZREVRANK, and ZREVRANGE all still just compare one double, and get a meaningful, product-correct tiebreak entirely as a side effect, with zero additional commands or application-side merge logic per query.

Why this is preferable to a secondary, separate comparison step: the alternative — store the raw score alone, and have the application break ties itself by fetching and comparing timestamps whenever two adjacent members turn out equal — reintroduces exactly the "recompute order at read time" cost this entire design exists to avoid (K); encoding the tiebreak into the sort key itself keeps the sorted set's standing, maintained order the single source of truth for the complete, already-correctly-broken ranking, with no extra read-time work at all.

R. Deep Dive 6 — Time-Windowed Leaderboards

Requirement this addresses: daily, weekly, and all-time leaderboards existing simultaneously and independently (D, E's clarifying question 5) — a requirement the single-ZSET-per-segment model (K) needs an explicit answer for, because "today's leaderboard" and "all-time" are genuinely different rankings over genuinely different (if overlapping) data.

Rotation and archival, closing the "old keys pile up forever" gap the chosen approach otherwise leaves open: a daily key gets a TTL slightly longer than 24 hours (so "yesterday's leaderboard" stays queryable for a defined grace period after the day rolls over, per F's confirmed requirement) and is written to durable storage (S) before it expires, so a closed window's final standings remain available indefinitely from the durable store even after Redis has reclaimed the memory — the same "Redis is the fast path, not the permanent archive" posture S establishes for individual scores, applied here to entire closed windows.

Why fanning a write out to multiple windows at once is an acceptable cost, not a hidden scaling problem: a player is typically active in at most three or four open windows simultaneously (their daily, their weekly, and all-time, per segment) — a small, bounded, constant fan-out per score update, nothing close to the shard fan-out O deliberately avoids for reads; the write-side cost here is a flat multiplier, not something that grows with dataset size.

S. Deep Dive 7 — Persistence & Durability

Requirement this addresses: durability of scores across a crash (D) — directly parallels P11-S's persistence deep dive for Distributed Cache, but starts from the opposite default: P11's cache is explicitly allowed to lose everything on a crash because the database behind it is already the source of truth; this design has no such database sitting behind it by default, so persistence here is not a warm-up optimization, it's the only thing standing between a crash and permanent data loss.

Why Redis alone, even with its own RDB/AOF persistence (P11-S), still isn't the right place to be the sole system of record: RDB snapshots lose everything since the last snapshot; AOF replay is slower to recover from and the log grows unboundedly — both are real, useful safety nets for Redis's own data, but neither one gives this design a durable store that's independently queryable, backup-able, and analyzable the way a real database is, and neither addresses the deeper issue that Redis's actual job here is to be fast, not to be the permanent archive.

The chosen approach: asynchronous write-behind to a durable store, off the client-facing critical path entirely. Every score update lands in the segment's ZSET synchronously (L) and is acknowledged to the client immediately off of that in-memory write alone; the same update is also enqueued (a lightweight, durable queue, per Prerequisites G) for asynchronous, batched persistence into a durable store — a relational or wide-column database, chosen for the same reasons Prerequisites B generally favors a durable, disk-backed store for anything that must survive a crash. The client never waits on this second write; it happens moments later, batched for efficiency, entirely decoupled from the read-latency budget (G) that the whole ZSET-based design exists to protect.

The direct, honest cost of this choice, named rather than glossed over: a crash between "acknowledged the client" and "the write-behind queue actually flushed to the durable store" loses that specific update from the durable copy, even though the client was already told it succeeded — the same asynchronous-replication trade-off P11-O names for a cache's leader-to-follower propagation, applied here to the Redis-to-durable-store direction instead. This is judged an acceptable, deliberate cost specifically because Redis's own in-memory ZSET is still correct and serving that score to every reader in the meantime — the durable copy briefly lagging behind doesn't corrupt or delay anything a live player actually sees; it only risks losing a handful of very recent updates in the rare case Redis itself is lost before the durable write catches up, a materially narrower and rarer failure than losing every score since the last snapshot.

Recovery, in priority order: if a live Redis replica exists for the segment (mirroring P11-O's leader-follower model per shard), promote it — no cold rebuild needed at all. If no replica survives, rebuild the ZSET directly from the durable store's current scores — slower than an in-memory promotion, but it never actually loses data, because the durable store, not Redis, is what this design ultimately trusts as the ground truth for "what score does this player actually have," even though Redis is what every live query is actually served from.

T. Full Solution at Scale — Final Architecture

Every read and write against a player's own segment shard is synchronous and fast, off the in-memory ZSET alone; global-rank aggregation and durable persistence both happen off that critical path, asynchronously, feeding back only an approximate cross-shard estimate and a crash-safe archival copy respectively — neither one ever slows down or blocks the request a player is actually waiting on.

U. Design Evolution

What stays stable throughout: the minimal submitScore/getRank/getTopN/getScoresAround contract from I — every refinement underneath changes how many shards a segment spans, how a rank is estimated across them, and where the durable copy of a score actually lives, never the basic shape of what a caller invokes.

V. Consistency Model

Two different consistency questions get two different answers in this design, and conflating them is the single most common mistake a candidate makes on this problem. The first question — "how fresh does the global leaderboard need to be for everyone" — tolerates real, brief staleness: once a segment is sharded (O), the approximate global rank shown to any given player is computed from per-shard histograms that refresh on a short interval, not instantaneously on every write, and that's an entirely acceptable, even invisible, trade given G's read volume and O's honest cost analysis. The second question — "how fresh does my own just-submitted score need to be, to me, specifically" — does not tolerate that same staleness at all: a player who just finished a match and immediately checks their rank is reading against the exact same shard their write just landed in (L), synchronously, with no aggregation delay in between — a direct instance of read-your-own-writes, guaranteed for free by the fact that a player's own score and a player's own rank query are always served from the same authoritative shard, never from the approximate cross-shard aggregation tier.

Why this split is the right one, not an inconsistency to apologize for: the exact global rank of the player currently sitting in 4,412,003rd place is a number nobody is watching closely enough to notice a few seconds of staleness in; that same player's own rank, immediately after they've just changed it, is a number they are watching closely, right now, and expect to be correct instantly. Serving the first from a cheap, periodically-refreshed aggregate and the second from the one authoritative shard that actually owns that player's data is exactly the right place to spend consistency effort and exactly the right place not to.

The direct contrast worth naming explicitly: unlike Payment System's single, non-negotiable strong-consistency stance for every transaction (P15-V), or Distributed Key-Value Store's fully tunable, per-keyspace PACELC dial (P18-V), this design doesn't tune consistency per use-case at all — it applies one specific, fixed rule (own writes are exact and immediate; everyone else's aggregate view is fast and approximate) uniformly, because the two different guarantees map cleanly onto two genuinely different queries, not two different configurable preferences over the same one.

W. Failure Scenarios

• A single shard's Redis node crashes: a live replica for that shard (mirroring P11-O's per-shard leader-follower model) is promoted, and the shard resumes serving reads and writes; if no replica survives, the shard's ZSET is rebuilt directly from the durable store (S), slower but lossless for anything that had already been write-behind persisted.
• The write-behind queue backs up or its consumer falls behind during a write spike: live reads and writes against the in-memory ZSETs are entirely unaffected (S's write-behind path is fully decoupled from the client-facing critical path); the durable store simply lags further behind until the queue catches up, a bounded, self-correcting delay rather than a client-visible failure.
• A shard's periodic histogram publish (O) is delayed or missed: the global-rank aggregation tier serves a slightly staler approximate rank estimate to every other shard's players querying their global position — each individual player's own exact, same-shard rank (V) is entirely unaffected, since it never depends on this path at all.
• A synchronized write burst (a raid boss, a mass match-end) exceeds a shard's coalesced write throughput momentarily: the bounded write queue (P) absorbs the peak with a small, low-millisecond queueing delay rather than dropping or rejecting writes; players see their new rank moments later than usual, never never at all.
• The durable store itself is briefly unreachable: write-behind records simply accumulate in the queue until it recovers; no client-facing operation is blocked by this at all, since the durable store is never on the read or acknowledged-write path (S).

X. Bottlenecks & Scaling Boundary

Y. Design Trade-Offs

Z. Common Bad Designs

• A relational table with ORDER BY score DESC LIMIT/OFFSET and COUNT(*) WHERE score > ? for rank. Works at small scale, and then degrades in a way that's structurally tied to dataset size, not fixable by adding more indexes or read replicas — the defining mistake on this problem (J).
• Treating Redis's own persistence (RDB/AOF) as sufficient durability on its own. Confuses "Redis won't lose data quite as badly" with "Redis is now a real system of record" — it still leaves a meaningful data-loss window on a crash and no independently queryable, backed-up copy of scores (S).
• Sharding a segment and then computing exact global rank via a live fan-out merge on every single request. Technically correct, but reintroduces almost the exact full-scan-per-query cost this design's entire structure was chosen to eliminate, just spread across a cluster of shards instead of one table (O).
• Reaching for P16's windowed-batch aggregation approach to absorb write bursts. Solves the throughput problem cleanly but directly trades away the near-real-time visibility a leaderboard's players actually expect — the correct fix for a different requirement than this one has (P).
• Leaving ties to whatever order the underlying structure happens to produce. Deterministic and reproducible, but arbitrary and unexplainable to the actual players it affects — a real, if quiet, correctness gap against the product's actual expectations (Q).

AA. Interviewer Follow-Ups

AB. Interviewer Evaluation Rubric

• Weak candidate: proposes a relational table with ORDER BY and COUNT(*) for rank, without recognizing that the rank query's cost scales with the dataset and will not survive real player counts.
• Mid-level: correctly reaches for Redis sorted sets and names ZADD/ZRANK/ZRANGE by their command names, but can't precisely explain why they're O(log N) rather than reciting "Redis is fast," and hasn't considered what happens once one segment outgrows a single node.
• Senior: explains the skip-list mechanics behind the ZSET's complexity bounds, proactively raises sharding once a segment's scale is pushed, and correctly identifies that a naive exact cross-shard rank merge reintroduces the very cost the sorted set was chosen to avoid.
• Staff+: designs the approximate global-rank aggregation tier explicitly, defends why approximation is the correct trade rather than a shortcut, distinguishes this problem's real-time-visibility requirement from Ad Click Aggregation's batch-tolerant one and explains precisely why the same batching fix doesn't transfer, and treats Redis's speed and its unsuitability as a sole system of record as two sides of the same coin rather than two unrelated facts.

AC. Interview Simulation

AD. Online Solution Comparison

AE. Interview Cheat Sheet