How to Build a Billion‑User Real‑Time Step Leaderboard Like WeChat Sports

Problem Overview

The interview question asks you to design a WeChat‑like step‑count ranking system that must support over a hundred million daily users, handle bursty write traffic, provide sub‑second friend‑ranking queries, and remain highly available.

Four Core Challenges

Write‑storm : Millions of users upload steps simultaneously, creating a massive write spike.

Query nightmare : Real‑time friend ranking with ORDER BY on a huge table would cripple MySQL.

Storage explosion : Storing billions of daily records would overwhelm disk and cost.

Relationship maze : Each user has a unique friend list, making a one‑size‑fits‑all ranking impossible.

First Axe – Asynchronous Write Path

Use a message queue (Kafka or RocketMQ) as a buffer. The front‑end service receives {userId, steps, timestamp} and immediately pushes the message to the queue without any DB write.

Downstream consumers pull messages at a controlled rate and update the ranking store. # ZADD leaderboard:2025-09-12 15000 user_id_A Redis ZSETs store the live leaderboard; ZADD overwrites old scores in O(log N) time, making updates fast and memory‑efficient.

Second Axe – Split Query Path (Static vs. Dynamic)

Separate static relationship data from dynamic step data.

Static “household register” : Query MySQL (sharded) to fetch a user's friend IDs. This is a low‑frequency, durable lookup.

Dynamic “scoreboard” : Pull all friends’ scores from Redis in bulk using pipelines or multithreading, avoiding 200 individual calls.

In‑memory ranking : Sort the small friend list in the service memory, then attach user profile info (nickname, avatar) from a cache.

Third Axe – Resilience and Hot‑User Handling

For users with millions of friends, pre‑compute their rankings and cache the result in a dedicated Redis key (pre‑calculation + cache strategy). A periodic background job refreshes these caches.

High availability is ensured by deploying Redis in master‑slave + Sentinel mode and retaining raw step messages in the MQ for replay if the cache fails.

Overall Architecture Summary

The solution combines asynchronous decoupling (MQ), hot‑cold data separation (Redis for real‑time scores, MySQL for relationships), and static‑dynamic split in queries. This yields a system that can ingest bursty writes, serve sub‑second friend rankings, and survive component failures.