System Design: Real-Time Game Leaderboard — Rankings for Millions of Players

-- Schema
CREATE TABLE scores (
  user_id  BIGINT PRIMARY KEY,
  score    BIGINT NOT NULL,
  updated  TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_score ON scores (score DESC);

-- Rank query for a given user
SELECT COUNT(*) + 1
FROM   scores
WHERE  score > (SELECT score FROM scores WHERE user_id = ?);

-- Top 100
SELECT user_id, score
FROM   scores
ORDER BY score DESC
LIMIT  100;

import time, redis

MAX_EPOCH = 9_999_999_999
SCORE_MULT = 10_000_000_000

def encode_score(points: int, ts: int = None) -> int:
    if ts is None:
        ts = int(time.time())
    return points * SCORE_MULT + (MAX_EPOCH - ts)

def decode_score(encoded: int) -> tuple[int, int]:
    points = encoded // SCORE_MULT
    ts     = MAX_EPOCH - (encoded % SCORE_MULT)
    return points, ts

r = redis.Redis()

def record_match_result(user_id: str, new_points: int):
    # Only update if new score is higher than current
    current_encoded = r.zscore('leaderboard', user_id)
    if current_encoded is not None:
        current_points, _ = decode_score(int(current_encoded))
        if new_points <= current_points:
            return   # no improvement
    encoded = encode_score(new_points)
    r.zadd('leaderboard', {user_id: encoded})

# Time-windowed leaderboard keys
import datetime

def weekly_key() -> str:
    iso = datetime.date.today().isocalendar()
    return 'leaderboard:' + str(iso.year) + '-W' + str(iso.week).zfill(2)

def zadd_with_ttl(pipe, user_id: str, encoded: int):
    key = weekly_key()
    pipe.zadd(key, {user_id: encoded})
    pipe.expire(key, 28 * 86400)  # expire after 4 weeks

# Friends leaderboard
def friends_leaderboard(user_id: str, friends: list[str]) -> list:
    pipe = r.pipeline(transaction=False)
    all_ids = friends + [user_id]
    for fid in all_ids:
        pipe.zscore('leaderboard', fid)
    scores = pipe.execute()
    pairs = [(all_ids[i], scores[i]) for i in range(len(all_ids)) if scores[i] is not None]
    pairs.sort(key=lambda x: x[1], reverse=True)
    return pairs

import asyncio, json, redis.asyncio as aioredis

r_async = aioredis.Redis()
prev_top100: list = []

async def top100_watcher(broadcast_fn):
    global prev_top100
    while True:
        raw = await r_async.zrevrange(
            'leaderboard', 0, 99, withscores=True
        )
        current = [(uid.decode(), int(sc)) for uid, sc in raw]
        if current != prev_top100:
            changes = compute_diff(prev_top100, current)
            await broadcast_fn(json.dumps({'type': 'top100_update', 'changes': changes}))
            prev_top100 = current
        await asyncio.sleep(0.5)   # poll every 500ms

def compute_diff(old, new):
    old_map = {uid: (i, sc) for i, (uid, sc) in enumerate(old)}
    new_map = {uid: (i, sc) for i, (uid, sc) in enumerate(new)}
    changes = []
    for uid, (new_rank, new_sc) in new_map.items():
        old_rank, old_sc = old_map.get(uid, (new_rank, new_sc))
        if new_rank != old_rank or new_sc != old_sc:
            changes.append({
                'userId': uid,
                'rank': new_rank + 1,
                'score': new_sc,
                'delta': new_sc - old_sc
            })
    return changes

def nearby_players(user_id: str, window: int = 5) -> dict:
    pipe = r.pipeline(transaction=False)
    pipe.zrevrank('leaderboard', user_id)
    pipe.zscore('leaderboard', user_id)
    pipe.zcard('leaderboard')
    rank_0indexed, score, total = pipe.execute()

    if rank_0indexed is None:
        return {'error': 'player not found'}

    start = max(0, rank_0indexed - window)
    end   = min(total - 1, rank_0indexed + window)

    raw = r.zrevrange('leaderboard', start, end, withscores=True)
    nearby = [
        {'rank': start + i + 1, 'userId': uid.decode(), 'score': int(sc)}
        for i, (uid, sc) in enumerate(raw)
    ]
    return {
        'myRank': rank_0indexed + 1,
        'myScore': int(score),
        'totalPlayers': total,
        'nearby': nearby
    }

Mobile Game Client
  |
  |  POST /match/result  (score, userId, matchId)
  v
Match Result Service          stateless, x20 pods
  |  validate, deduplicate (matchId → idempotency key)
  |  compute new score (points + bonus)
  v
Redis Cluster
  |  ZADD leaderboard:global   encoded_score   userId
  |  ZADD leaderboard:weekly   encoded_score   userId
  |  ZADD leaderboard:region:X encoded_score   userId
  |
  +--------+----------------+
  |                        |
  v                        v
Rank API                Top-100 Watcher
  |  ZREVRANK               |  poll every 500ms
  |  ZREVRANGE nearby       |  compute diff
  |  ZSCORE friends         v
  v                   Redis Pub/Sub
REST / gRPC clients         |
                            v
                      WebSocket Fan-out
                            |  x5 nodes, 20K conn each
                            v
                       Live Clients (top-100 watchers)

Write path also fans to:
PostgreSQL  →  match history, audit log
Kafka       →  analytics, anti-cheat pipeline