Real-Time Leaderboards with SQL: Gaming and Competition Analytics

Real-time leaderboards require continuous ranking of players based on scores, achievements, or metrics that change with every game event. A streaming database maintains leaderboards as SQL materialized views that update instantly — no batch recalculation, no Redis scripting.

Leaderboard with RisingWave

-- Ingest game events
CREATE SOURCE game_events (player_id VARCHAR, event_type VARCHAR,
  points INT, game_id VARCHAR, ts TIMESTAMP WITH TIME ZONE)
WITH (connector='kafka', topic='game-events', properties.bootstrap.server='kafka:9092')
FORMAT PLAIN ENCODE JSON;

-- Global leaderboard
CREATE MATERIALIZED VIEW leaderboard AS
SELECT player_id,
  SUM(points) as total_score,
  COUNT(*) FILTER (WHERE event_type='kill') as kills,
  COUNT(*) FILTER (WHERE event_type='death') as deaths,
  RANK() OVER (ORDER BY SUM(points) DESC) as rank
FROM game_events GROUP BY player_id;

-- Daily leaderboard
CREATE MATERIALIZED VIEW daily_leaderboard AS
SELECT player_id, SUM(points) as daily_score,
  RANK() OVER (ORDER BY SUM(points) DESC) as rank
FROM game_events WHERE ts > CURRENT_DATE::TIMESTAMP
GROUP BY player_id;

Query via PostgreSQL: SELECT * FROM leaderboard LIMIT 100;

Why Not Redis?

Redis sorted sets are fast but require application-level logic for complex scoring, time windows, and multi-metric rankings. SQL expresses these naturally. RisingWave also provides persistence (S3) and consistency that Redis doesn't.

Frequently Asked Questions

How fast do leaderboards update?

RisingWave materialized views update within milliseconds of each game event. Players see their rank change in real time.

Can I build tournament brackets with this?

Yes. Use materialized views with window functions (RANK, ROW_NUMBER) to compute elimination brackets, round standings, and head-to-head records in real time.