Real-Time Leaderboards, Scoreboards, Matchmaking, and Odds with Streaming SQL

Real-time leaderboards, scoreboards, matchmaking profiles, and odds with streaming SQL means every ranking, score, skill rating, quest funnel, guild health score, and in-play probability is a materialized view that RisingWave updates incrementally the moment a scoring event, play, match result, or trade arrives. This guide builds the live systems a competitive game, sports product, or prediction market runs on, in a PostgreSQL-compatible streaming database, starting with a verified leaderboard tutorial and extending the same pattern to sports scoreboards, matchmaking, quests and achievements, multiplayer match processing, social features, client telemetry, and odds calculation.

Why Live Systems Break on Traditional Architectures

Every competitive game, fitness app, and sales dashboard shares one core problem: ranking users by a score that changes constantly. The two usual answers both fail at scale. Query-time aggregation (SELECT player_id, SUM(points) ... ORDER BY 2 DESC LIMIT 10 on every request) rescans the events table and climbs from milliseconds to seconds as events grow. Application-managed state in a Redis sorted set is fast to read but has to be kept consistent with the event log, rebuilt after any bug fix, and duplicated for every ranking dimension.

The same tension appears everywhere in live products. A sports scoreboard polls a database or manages WebSocket state by hand. A matchmaker uses skill ratings computed yesterday. A quest funnel shows up in a weekly report after thousands of players dropped at the same objective. Odds refreshed every minute leave an arbitrage window that sharp bettors exploit.

A streaming materialized view removes the tradeoff. You define the ranking, score, or probability once as SQL. RisingWave maintains the result as events arrive, recomputing only the affected rows, and your API reads it over the PostgreSQL protocol in milliseconds. RisingWave is open source under Apache 2.0, written in Rust, and persists view state to S3, so state size is proportional to the number of players or markets rather than the number of events, and nothing is recomputed on restart.

Tutorial: A Leaderboard in Four Views

Start with a table so you can follow along without Kafka. In production the same schema is a Kafka source, and every view below works unchanged.

CREATE TABLE game_events (
    event_id     VARCHAR,
    player_id    VARCHAR,
    player_name  VARCHAR,
    event_type   VARCHAR,     -- 'kill', 'objective', 'assist'
    points       INT,
    game_mode    VARCHAR,
    event_time   TIMESTAMPTZ
);

INSERT INTO game_events VALUES
    ('e001', 'p1', 'ShadowNinja',  'kill',      100, 'ranked', '2026-03-29 10:00:00+00'),
    ('e002', 'p2', 'DragonSlayer', 'kill',      100, 'ranked', '2026-03-29 10:00:05+00'),
    ('e003', 'p1', 'ShadowNinja',  'kill',      100, 'ranked', '2026-03-29 10:00:10+00'),
    ('e004', 'p3', 'CosmicRay',    'objective', 250, 'ranked', '2026-03-29 10:00:15+00'),
    ('e005', 'p2', 'DragonSlayer', 'kill',      100, 'ranked', '2026-03-29 10:00:20+00'),
    ('e006', 'p4', 'IronWolf',     'kill',      100, 'ranked', '2026-03-29 10:00:25+00'),
    ('e007', 'p1', 'ShadowNinja',  'objective', 250, 'ranked', '2026-03-29 10:00:30+00'),
    ('e008', 'p5', 'PixelQueen',   'kill',      100, 'ranked', '2026-03-29 10:00:35+00'),
    ('e010', 'p2', 'DragonSlayer', 'objective', 250, 'ranked', '2026-03-29 10:00:45+00'),
    ('e013', 'p5', 'PixelQueen',   'objective', 250, 'ranked', '2026-03-29 10:01:00+00'),
    ('e019', 'p7', 'StarForge',    'kill',      100, 'ranked', '2026-03-29 10:01:30+00'),
    ('e020', 'p1', 'ShadowNinja',  'kill',      100, 'ranked', '2026-03-29 10:01:35+00');

The first view aggregates events into per-player totals. The second adds rank with a window function. RisingWave requires a PARTITION BY in window functions inside materialized views; PARTITION BY 1::INT ranks across all rows.

CREATE MATERIALIZED VIEW player_total_scores AS
SELECT player_id, player_name,
       SUM(points)                                        AS total_score,
       COUNT(*)                                           AS total_events,
       COUNT(*) FILTER (WHERE event_type = 'kill')        AS total_kills,
       COUNT(*) FILTER (WHERE event_type = 'objective')   AS total_objectives,
       MAX(event_time)                                    AS last_active
FROM game_events
GROUP BY player_id, player_name;

CREATE MATERIALIZED VIEW leaderboard_with_ranks AS
SELECT player_id, player_name, total_score, total_kills, total_objectives, last_active,
       ROW_NUMBER() OVER (PARTITION BY 1::INT ORDER BY total_score DESC) AS rank
FROM player_total_scores;

Query SELECT rank, player_name, total_score FROM leaderboard_with_ranks ORDER BY rank and ShadowNinja leads with 650. Now insert five events for StarForge worth 800 points and query again: StarForge is rank 1 with 900, and no application code ran. Only StarForge's total and the affected ranks were recomputed.

Real games need several rankings. Per-mode ranks use PARTITION BY game_mode; efficiency ranks by points per event; a combined view carries several ROW_NUMBER columns at once so a player can be first in score and second in kills in the same row.

CREATE MATERIALIZED VIEW mode_leaderboard AS
SELECT game_mode, player_id, player_name,
       SUM(points)                                        AS mode_score,
       COUNT(*) FILTER (WHERE event_type = 'kill')        AS mode_kills,
       ROW_NUMBER() OVER (PARTITION BY game_mode ORDER BY SUM(points) DESC) AS mode_rank
FROM game_events
GROUP BY game_mode, player_id, player_name;

CREATE MATERIALIZED VIEW leaderboard_multi AS
SELECT player_id, player_name,
       SUM(points)                                                          AS total_score,
       COUNT(*) FILTER (WHERE event_type = 'kill')                          AS kills,
       ROUND(SUM(points)::NUMERIC / COUNT(*), 1)                            AS avg_points,
       ROW_NUMBER() OVER (PARTITION BY 1::INT ORDER BY SUM(points) DESC)    AS score_rank,
       ROW_NUMBER() OVER (PARTITION BY 1::INT ORDER BY COUNT(*) FILTER (WHERE event_type = 'kill') DESC) AS kills_rank
FROM game_events
GROUP BY player_id, player_name;

Players care more about "top scores this week" than all-time. A tumbling window per day or week gives time-bounded boards, and RANK() handles ties the way players expect:

CREATE MATERIALIZED VIEW leaderboard_daily AS
SELECT window_start, window_end, player_id, player_name, game_mode,
       SUM(points) AS daily_score,
       RANK() OVER (PARTITION BY game_mode, window_start ORDER BY SUM(points) DESC) AS daily_rank
FROM TUMBLE(game_events, event_time, INTERVAL '1 DAY')
GROUP BY window_start, window_end, player_id, player_name, game_mode;

Late scores from reconnecting mobile clients land in the right window through watermarks. For the top 100 only, add LIMIT to the query so the optimizer bounds the ranking work.

Serving a Leaderboard

Any PostgreSQL client works. The query below completes in under five milliseconds whether the events table holds a thousand rows or a hundred million, because the view is precomputed:

import psycopg2
conn = psycopg2.connect(host="localhost", port=4566, user="root", dbname="dev")
cur = conn.cursor()
cur.execute("SELECT rank, player_name, total_score FROM leaderboard_with_ranks ORDER BY rank LIMIT 10")
for rank, name, score in cur.fetchall():
    print(f"#{rank} {name}: {score}")

To serve from an existing PostgreSQL or to fan out ranking changes, add a sink. Only the rows whose rank changed are sent:

CREATE SINK leaderboard_to_postgres FROM leaderboard_with_ranks
WITH (connector = 'jdbc', jdbc.url = 'jdbc:postgresql://your-db:5432/gamedb',
      table.name = 'leaderboard', type = 'upsert', primary_key = 'player_id');

CREATE SINK leaderboard_to_kafka FROM leaderboard_daily
WITH (connector = 'kafka', topic = 'game.leaderboard.updates', properties.bootstrap.server = 'kafka:9092')
FORMAT UPSERT ENCODE JSON;

Live Sports Scoreboards, Player Stats, Fouls, and Standings

A sports scoreboard is the same pattern with three tables: teams, games, and a play-by-play event stream. The scoreboard view splits points into home and away by looking up which team is home; the player stats view uses conditional aggregation; the foul tracker labels players approaching disqualification; the standings view counts only games marked FINAL, so live games never affect records until they end.

CREATE TABLE sports_teams (team_id INT PRIMARY KEY, team_name VARCHAR, city VARCHAR, sport VARCHAR);
CREATE TABLE sports_games (game_id INT PRIMARY KEY, home_team INT, away_team INT, game_date DATE, venue VARCHAR, status VARCHAR);
CREATE TABLE sports_events (
    event_id BIGINT, game_id INT, event_type VARCHAR, team_id INT, player_name VARCHAR, points INT, event_ts TIMESTAMPTZ
);

CREATE MATERIALIZED VIEW sports_game_scores AS
SELECT e.game_id, g.venue, g.status, ht.team_name AS home_team, at.team_name AS away_team,
       SUM(CASE WHEN e.team_id = g.home_team THEN e.points ELSE 0 END) AS home_score,
       SUM(CASE WHEN e.team_id = g.away_team THEN e.points ELSE 0 END) AS away_score,
       MAX(e.event_ts) AS last_event_ts
FROM sports_events e
JOIN sports_games g  ON e.game_id = g.game_id
JOIN sports_teams ht ON g.home_team = ht.team_id
JOIN sports_teams at ON g.away_team = at.team_id
GROUP BY e.game_id, g.venue, g.status, g.home_team, g.away_team, ht.team_name, at.team_name;

CREATE MATERIALIZED VIEW sports_player_stats AS
SELECT e.player_name, t.team_name, e.game_id,
       SUM(e.points)                                            AS total_points,
       COUNT(*) FILTER (WHERE e.event_type = 'TWO_POINTER')     AS two_pointers,
       COUNT(*) FILTER (WHERE e.event_type = 'THREE_POINTER')   AS three_pointers,
       COUNT(*) FILTER (WHERE e.event_type = 'FREE_THROW')      AS free_throws,
       COUNT(*) FILTER (WHERE e.event_type = 'FOUL')            AS fouls
FROM sports_events e
JOIN sports_teams t ON e.team_id = t.team_id
GROUP BY e.player_name, t.team_name, e.game_id;

CREATE MATERIALIZED VIEW sports_foul_summary AS
SELECT game_id, team_name, player_name, fouls AS foul_count,
       CASE WHEN fouls >= 5 THEN 'FOULED_OUT' WHEN fouls = 4 THEN 'FOUL_TROUBLE' WHEN fouls = 3 THEN 'WATCH' ELSE 'OK' END AS foul_status
FROM sports_player_stats
WHERE fouls > 0;

CREATE MATERIALIZED VIEW sports_standings AS
WITH finals AS (
    SELECT s.game_id, g.home_team, g.away_team, s.home_score, s.away_score
    FROM sports_game_scores s
    JOIN sports_games g ON s.game_id = g.game_id
    WHERE g.status = 'FINAL'
),
records AS (
    SELECT home_team AS team_id, (home_score > away_score)::INT AS win, (home_score < away_score)::INT AS loss,
           home_score AS points_for, away_score AS points_against FROM finals
    UNION ALL
    SELECT away_team, (away_score > home_score)::INT, (away_score < home_score)::INT, away_score, home_score FROM finals
)
SELECT t.team_name, t.city, SUM(r.win) AS wins, SUM(r.loss) AS losses,
       SUM(r.points_for) AS total_points_scored, SUM(r.points_for) - SUM(r.points_against) AS point_differential
FROM records r
JOIN sports_teams t ON r.team_id = t.team_id
GROUP BY t.team_name, t.city;

Insert a three-pointer and a foul for one player and the scoreboard, their stat line, and their foul status all update together. Set a game to FINAL with an UPDATE on sports_games and the standings appear. In production, sports_events is a Kafka source from the tracking provider or a PostgreSQL CDC table from the stats database, and the views are unchanged.

Matchmaking Profiles

Skill-based matchmaking is only as good as the data behind it. A rating updated daily matches a player on a 20-game winning streak against opponents calibrated to yesterday. A seven-day hopping window over match results, sliding every 30 minutes, keeps recent form current, and a temporal join with the MMR table from the operational database blends it into one composite score the matchmaker queries by range.

CREATE SOURCE match_results (
    match_id VARCHAR, player_id BIGINT, team_id VARCHAR, outcome VARCHAR,
    kills INT, deaths INT, assists INT, damage_dealt INT, match_duration INT,
    game_mode VARCHAR, region VARCHAR, completed_at TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'game.match.results',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE TABLE player_ratings (
    player_id BIGINT PRIMARY KEY, current_mmr FLOAT, games_played INT, region VARCHAR, preferred_mode VARCHAR, last_updated TIMESTAMPTZ
) WITH (
    connector = 'postgres-cdc',
    hostname = 'postgres.internal', port = '5432',
    username = 'rwuser', password = 'secret',
    database.name = 'game_db', schema.name = 'public', table.name = 'player_ratings'
);

CREATE MATERIALIZED VIEW player_performance_7d AS
SELECT window_start, window_end, player_id, game_mode, region,
       COUNT(*)                                                                 AS matches,
       ROUND(COUNT(*) FILTER (WHERE outcome = 'win')::DECIMAL / NULLIF(COUNT(*), 0) * 100, 2) AS win_rate_pct,
       AVG(kills) AS avg_kills, AVG(deaths) AS avg_deaths, AVG(damage_dealt) AS avg_damage,
       ROUND(AVG(kills)::DECIMAL / NULLIF(AVG(deaths), 0), 2)                   AS kd_ratio
FROM HOP(match_results, completed_at, INTERVAL '30 MINUTES', INTERVAL '7 DAYS')
GROUP BY window_start, window_end, player_id, game_mode, region;

CREATE MATERIALIZED VIEW matchmaking_profile AS
SELECT p.player_id, p.current_mmr, p.region, p.preferred_mode,
       COALESCE(m.win_rate_pct, 50.0) AS recent_win_rate,
       COALESCE(m.kd_ratio, 1.0)      AS recent_kd_ratio,
       COALESCE(m.matches, 0)         AS recent_matches,
       p.current_mmr * 0.7 + COALESCE(m.win_rate_pct, 50.0) * 3 + COALESCE(m.kd_ratio, 1.0) * 50 AS composite_score
FROM player_ratings FOR SYSTEM_TIME AS OF NOW() p
LEFT JOIN player_performance_7d m
  ON p.player_id = m.player_id AND p.region = m.region AND p.preferred_mode = m.game_mode
 AND m.window_end > NOW() - INTERVAL '30 MINUTES';

CREATE MATERIALIZED VIEW queue_health AS
SELECT window_start, window_end, region, game_mode,
       COUNT(DISTINCT match_id)     AS matches_completed,
       AVG(match_duration)          AS avg_match_duration_secs,
       STDDEV(match_duration)       AS match_duration_stddev
FROM TUMBLE(match_results, completed_at, INTERVAL '5 MINUTES')
GROUP BY window_start, window_end, region, game_mode;

The matchmaking service subscribes to an upsert sink of matchmaking_profile and updates its in-memory candidate pool in under a second. High standard deviation in match duration in queue_health is a match quality signal: one team is dominating quickly.

Quests and Achievements

Quest completion rates in a weekly report arrive after thousands of players have dropped at the same objective. A funnel view joined to design metadata flags any quest whose completion rate is far below the designer's expectation, hourly activity shows the exact objective where progress stalls, and the achievement view surfaces players at 90 percent or more who have not unlocked yet, the best audience for a nudge.

CREATE SOURCE quest_events (
    player_id BIGINT, quest_id VARCHAR, quest_type VARCHAR,
    event_type VARCHAR,          -- 'quest_started', 'objective_completed', 'quest_completed', 'quest_abandoned'
    objective_id VARCHAR, progress_value INT, required_value INT, event_time TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'quest-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE TABLE quest_metadata (
    quest_id VARCHAR PRIMARY KEY, quest_name VARCHAR, chapter INT, designed_difficulty VARCHAR, expected_completion_pct DOUBLE PRECISION
);

CREATE MATERIALIZED VIEW quest_completion_funnel AS
SELECT q.quest_id, m.quest_name, m.designed_difficulty, m.expected_completion_pct,
       COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_started')    AS players_started,
       COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_completed')  AS players_completed,
       COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_abandoned')  AS players_abandoned,
       ROUND(COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_completed')::NUMERIC
             / NULLIF(COUNT(DISTINCT player_id) FILTER (WHERE event_type = 'quest_started'), 0) * 100, 2) AS completion_rate_pct
FROM quest_events q
LEFT JOIN quest_metadata m ON q.quest_id = m.quest_id
GROUP BY q.quest_id, m.quest_name, m.designed_difficulty, m.expected_completion_pct;

CREATE MATERIALIZED VIEW objective_bottlenecks AS
SELECT window_start, quest_id, objective_id,
       COUNT(DISTINCT player_id)                                                AS active_players,
       COUNT(*) FILTER (WHERE event_type = 'objective_completed')               AS completions,
       AVG(progress_value::DOUBLE PRECISION / NULLIF(required_value, 0) * 100)  AS avg_progress_pct
FROM TUMBLE(quest_events, event_time, INTERVAL '1 HOUR')
GROUP BY window_start, quest_id, objective_id;

CREATE SOURCE achievement_events (
    player_id BIGINT, achievement_id VARCHAR, achievement_tier VARCHAR,
    progress_value INT, required_value INT, is_unlocked BOOLEAN, event_time TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'achievement-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW achievement_unlock_rates AS
SELECT window_start::DATE AS unlock_date, achievement_id, achievement_tier,
       COUNT(DISTINCT player_id) FILTER (WHERE is_unlocked)                                     AS new_unlocks,
       COUNT(DISTINCT player_id)                                                                AS players_with_progress,
       COUNT(DISTINCT player_id) FILTER (WHERE progress_value::DOUBLE PRECISION / NULLIF(required_value, 0) >= 0.9 AND NOT is_unlocked) AS near_completion_players
FROM TUMBLE(achievement_events, event_time, INTERVAL '1 DAY')
GROUP BY window_start, achievement_id, achievement_tier;

Map the tutorial as a sequence of main quests and quest_completion_funnel filtered to them is the most important retention funnel in the game. For daily quests, align the tumbling window to UTC midnight.

Multiplayer Match Processing: Live Stats, Match Summaries, and the Kill Feed

A mid-size competitive title with 100,000 concurrent players generates up to two million events a minute. The same stream feeds a windowed live leaderboard enriched with player profiles, a per-match summary for the end-of-match screen, and a 30-second sliding kill feed that drops old events automatically.

CREATE SOURCE match_events (
    event_id VARCHAR, match_id VARCHAR, player_id BIGINT, opponent_id BIGINT,
    event_type VARCHAR,          -- 'kill', 'death', 'assist', 'capture', 'purchase'
    weapon_id VARCHAR, map_id VARCHAR, value NUMERIC, event_time TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'game-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE TABLE player_profiles (player_id BIGINT PRIMARY KEY, display_name VARCHAR, rank_tier VARCHAR, region VARCHAR);

CREATE MATERIALIZED VIEW leaderboard_hourly AS
SELECT e.player_id, p.display_name, p.rank_tier, p.region, window_start, window_end,
       COUNT(*) FILTER (WHERE e.event_type = 'kill')      AS kills,
       COUNT(*) FILTER (WHERE e.event_type = 'death')     AS deaths,
       COUNT(*) FILTER (WHERE e.event_type = 'capture')   AS captures,
       SUM(e.value)                                        AS total_score,
       COUNT(*) FILTER (WHERE e.event_type = 'kill')::NUMERIC / NULLIF(COUNT(*) FILTER (WHERE e.event_type = 'death'), 0) AS kd_ratio
FROM TUMBLE(match_events, event_time, INTERVAL '1 HOUR') e
JOIN player_profiles FOR SYSTEM_TIME AS OF NOW() p ON e.player_id = p.player_id
GROUP BY e.player_id, p.display_name, p.rank_tier, p.region, window_start, window_end;

CREATE MATERIALIZED VIEW match_summary AS
SELECT match_id, map_id,
       MIN(event_time) AS match_start, MAX(event_time) AS last_event_time,
       COUNT(DISTINCT player_id)                                        AS player_count,
       COUNT(*) FILTER (WHERE event_type = 'kill')                      AS total_kills,
       COUNT(*) FILTER (WHERE event_type = 'capture')                   AS total_captures,
       SUM(value)                                                       AS total_score_generated
FROM match_events
GROUP BY match_id, map_id;

CREATE MATERIALIZED VIEW recent_notable_events AS
SELECT e.event_id, e.match_id, p.display_name AS player_name, op.display_name AS opponent_name, e.weapon_id, e.event_type, e.event_time
FROM HOP(match_events, event_time, INTERVAL '1 SECOND', INTERVAL '30 SECONDS') e
JOIN player_profiles FOR SYSTEM_TIME AS OF NOW() p  ON e.player_id = p.player_id
JOIN player_profiles FOR SYSTEM_TIME AS OF NOW() op ON e.opponent_id = op.player_id
WHERE e.event_type IN ('kill', 'capture') AND e.value >= 100;

The game server queries match_summary by match ID the moment a match ends. Partition the Kafka topic by match so per-match aggregation stays local to a worker.

Social Features: Guild Health and Friend Formation

Social features are the retention engine, and they decay silently without measurement. A 15-minute guild health score weighs cooperative completions and chat against departures; a weekly per-player view classifies new players as connected, partially connected, or isolated; a join to guild metadata produces the at-risk list a community manager acts on.

CREATE SOURCE social_events (
    event_id VARCHAR, player_id BIGINT, target_id BIGINT, guild_id BIGINT,
    event_type VARCHAR,          -- 'guild_join', 'guild_leave', 'friend_add', 'friend_remove', 'chat_message', 'coop_complete'
    event_time TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'social-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE TABLE guilds (guild_id BIGINT PRIMARY KEY, guild_name VARCHAR, region VARCHAR, created_at TIMESTAMPTZ);

CREATE MATERIALIZED VIEW guild_health_15min AS
SELECT guild_id, window_start, window_end,
       COUNT(DISTINCT player_id)                                 AS active_members,
       COUNT(*) FILTER (WHERE event_type = 'coop_complete')      AS coop_completions,
       COUNT(*) FILTER (WHERE event_type = 'chat_message')       AS chat_messages,
       COUNT(*) FILTER (WHERE event_type = 'guild_leave')        AS members_left,
       COUNT(*) FILTER (WHERE event_type = 'guild_join')         AS members_joined,
       COUNT(*) FILTER (WHERE event_type = 'coop_complete') * 3
     + COUNT(*) FILTER (WHERE event_type = 'chat_message')
     - COUNT(*) FILTER (WHERE event_type = 'guild_leave') * 5    AS health_score
FROM TUMBLE(social_events, event_time, INTERVAL '15 MINUTES')
WHERE guild_id IS NOT NULL
GROUP BY guild_id, window_start, window_end;

CREATE MATERIALIZED VIEW at_risk_guilds AS
SELECT h.guild_id, g.guild_name, g.region, h.window_start, h.active_members, h.members_left, h.health_score,
       CASE WHEN h.health_score < 0                       THEN 'critical'
            WHEN h.members_left > h.members_joined * 2    THEN 'high_churn'
            WHEN h.active_members < 3                     THEN 'inactive'
            ELSE 'healthy' END AS guild_status
FROM guild_health_15min h
JOIN guilds g ON h.guild_id = g.guild_id;

CREATE MATERIALIZED VIEW friend_formation_weekly AS
SELECT player_id, window_start::DATE AS cohort_week,
       COUNT(*) FILTER (WHERE event_type = 'friend_add')     AS friends_added,
       COUNT(*) FILTER (WHERE event_type = 'coop_complete')  AS coops_with_friends,
       CASE WHEN COUNT(*) FILTER (WHERE event_type = 'friend_add') >= 3 THEN 'connected'
            WHEN COUNT(*) FILTER (WHERE event_type = 'friend_add') >= 1 THEN 'partially_connected'
            ELSE 'isolated' END AS social_status
FROM TUMBLE(social_events, event_time, INTERVAL '7 DAYS')
GROUP BY player_id, window_start;

A guild under five active members triggers a recruitment banner for high-activity players in the region; an isolated player after three sessions gets a friend suggestion; a spike in departures with low chat is a moderation signal.

Client Telemetry: Performance Percentiles and Version Regressions

Is the game working well for players right now? Client telemetry answers it only if it is processed continuously. Percentiles per minute by version, platform, and region expose the tail that averages hide, and a self-join between versions flags a regression as soon as both have enough samples.

CREATE SOURCE game_telemetry (
    client_id VARCHAR, player_id BIGINT, session_id VARCHAR, platform VARCHAR, device_model VARCHAR,
    os_version VARCHAR, game_version VARCHAR, region VARCHAR,
    metric_type VARCHAR,         -- 'fps', 'load_ms', 'latency_ms', 'crash'
    metric_value FLOAT, level_id VARCHAR, recorded_at TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'game.telemetry.client',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW telemetry_performance AS
SELECT window_start, window_end, game_version, platform, region, metric_type,
       COUNT(*)                                                    AS sample_count,
       AVG(metric_value)                                           AS avg_value,
       PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY metric_value)  AS p50,
       PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY metric_value)  AS p90,
       PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY metric_value)  AS p99
FROM TUMBLE(game_telemetry, recorded_at, INTERVAL '1 MINUTE')
WHERE metric_type <> 'crash'
GROUP BY window_start, window_end, game_version, platform, region, metric_type;

CREATE MATERIALIZED VIEW version_regression_check AS
SELECT curr.window_start, curr.game_version AS current_version, prev.game_version AS previous_version,
       curr.platform, curr.metric_type, curr.p99 AS current_p99, prev.p99 AS prev_p99,
       ROUND(((curr.p99 - prev.p99) / NULLIF(prev.p99, 0) * 100)::NUMERIC, 2) AS pct_change
FROM telemetry_performance curr
JOIN telemetry_performance prev
  ON curr.platform = prev.platform AND curr.metric_type = prev.metric_type
 AND curr.window_start = prev.window_start AND curr.game_version > prev.game_version
WHERE curr.sample_count > 1000 AND prev.sample_count > 1000;

A p99 frame rate under 30 on a high-end device after a patch, or a load time p90 rising for one level, is a regression visible within a minute. Aggregated telemetry sinks to Iceberg for long-term trend analysis while RisingWave serves the live dashboard.

Odds for Betting and Prediction Markets

Odds are implied probability. In a prediction market the price is the probability: a YES contract at $0.40 is 40 percent. The same windowed and ranked views that power a leaderboard power odds calculation, and the stakes are higher, because odds refreshed every minute leave an arbitrage window.

Order-book odds take the best bid and ask per market; the midpoint is the best estimate of true probability and the spread measures uncertainty and liquidity. Volume-weighted odds over several horizons show where money is actually flowing, and line movement against earlier prices catches sharp action and manipulation.

CREATE SOURCE order_book_events (
    market_id VARCHAR, side VARCHAR, side_outcome VARCHAR, price NUMERIC, quantity NUMERIC, event_time TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'prediction.orderbook', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE market_trades (
    market_id VARCHAR, platform VARCHAR, event_id VARCHAR, side VARCHAR, price NUMERIC, quantity NUMERIC, trade_time TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'prediction.trades', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW live_odds AS
SELECT market_id,
       MAX(price) FILTER (WHERE side = 'BID' AND side_outcome = 'YES')  AS best_yes_bid,
       MIN(price) FILTER (WHERE side = 'ASK' AND side_outcome = 'YES')  AS best_yes_ask,
       (MAX(price) FILTER (WHERE side = 'BID' AND side_outcome = 'YES')
      + MIN(price) FILTER (WHERE side = 'ASK' AND side_outcome = 'YES')) / 2.0 AS midpoint_probability,
       MIN(price) FILTER (WHERE side = 'ASK' AND side_outcome = 'YES')
     - MAX(price) FILTER (WHERE side = 'BID' AND side_outcome = 'YES')  AS spread
FROM order_book_events
GROUP BY market_id;

CREATE MATERIALIZED VIEW vwap_odds AS
SELECT market_id,
       SUM(price * quantity) FILTER (WHERE trade_time > NOW() - INTERVAL '5 MINUTES')
         / NULLIF(SUM(quantity) FILTER (WHERE trade_time > NOW() - INTERVAL '5 MINUTES'), 0)  AS vwap_5min,
       SUM(price * quantity) FILTER (WHERE trade_time > NOW() - INTERVAL '1 HOUR')
         / NULLIF(SUM(quantity) FILTER (WHERE trade_time > NOW() - INTERVAL '1 HOUR'), 0)     AS vwap_1h,
       SUM(price * quantity) FILTER (WHERE trade_time > NOW() - INTERVAL '24 HOURS')
         / NULLIF(SUM(quantity) FILTER (WHERE trade_time > NOW() - INTERVAL '24 HOURS'), 0)   AS vwap_24h,
       STDDEV(price) FILTER (WHERE trade_time > NOW() - INTERVAL '1 HOUR')                    AS volatility_1h
FROM market_trades
WHERE side = 'YES'
GROUP BY market_id;

CREATE MATERIALIZED VIEW cross_market_odds AS
SELECT event_id,
       MAX(price) FILTER (WHERE platform = 'polymarket')  AS polymarket_odds,
       MAX(price) FILTER (WHERE platform = 'kalshi')      AS kalshi_odds,
       MAX(price) FILTER (WHERE platform = 'betfair')     AS betfair_odds,
       AVG(price)                                         AS consensus_probability,
       MAX(price) - MIN(price)                            AS max_spread,
       MAX(price) - MIN(price) > 0.05                     AS arbitrage_flag
FROM market_trades
WHERE trade_time > NOW() - INTERVAL '5 MINUTES'
GROUP BY event_id;

In-play sports odds are the hardest case: the probability must move within milliseconds of a goal, turnover, or penalty. The scoreboard views above give live game state; joining state and a momentum signal to a precomputed win probability model keyed by score and clock gives a live, adjustable probability:

CREATE MATERIALIZED VIEW live_game_state AS
SELECT s.game_id, s.home_team, s.away_team, s.home_score, s.away_score, s.last_event_ts,
       COUNT(*) FILTER (WHERE e.team_id = g.home_team AND e.event_ts > NOW() - INTERVAL '5 MINUTES') AS home_pressure_5min,
       COUNT(*) FILTER (WHERE e.team_id = g.away_team AND e.event_ts > NOW() - INTERVAL '5 MINUTES') AS away_pressure_5min
FROM sports_game_scores s
JOIN sports_games g ON s.game_id = g.game_id
JOIN sports_events e ON e.game_id = s.game_id
GROUP BY s.game_id, s.home_team, s.away_team, s.home_score, s.away_score, s.last_event_ts, g.home_team, g.away_team;

CREATE TABLE win_probability_model (
    home_score INT, away_score INT, elapsed_min_start INT, elapsed_min_end INT,
    home_win_prob NUMERIC, draw_prob NUMERIC, away_win_prob NUMERIC,
    PRIMARY KEY (home_score, away_score, elapsed_min_start)
);

CREATE MATERIALIZED VIEW normalized_odds AS
SELECT market_id, outcome, raw_price,
       raw_price / SUM(raw_price) OVER (PARTITION BY market_id) AS normalized_probability
FROM outcome_prices;

Multi-outcome markets such as a tournament winner need probabilities that sum to one, which the window function above provides. Correlated markets ("Team A wins game 1" versus "Team A wins the series") can be checked for logical inconsistency with a join, and a momentum adjustment, for instance a 5 percent boost when one side has twice the pressure, is a CASE expression over live_game_state. At Super Bowl scale, more than 100,000 bets per second, the work spreads across compute nodes with state on S3, and compute can be added for the event and removed afterward.

Batch and Cache Architectures vs Streaming SQL for Live Systems

Frequently Asked Questions

Why does RisingWave require PARTITION BY in window functions inside materialized views? Incremental maintenance of a ranking needs a partition key to bound the rows that change when one input changes. PARTITION BY 1::INT ranks the whole set and is fine for global boards; partitioning by mode, region, or window keeps ranking work local.

How does the leaderboard handle millions of players? State is proportional to distinct players, not events, so ten million players is a few gigabytes of view state on S3. Kafka partitions are consumed in parallel, and LIMIT in the serving query lets the optimizer bound top-N work.

Can one event stream feed both the leaderboard and anti-cheat? Yes. Anti-cheat views such as accuracy and movement checks read the same game_events or match_events source. The companion guide on anti-cheat, account takeover, and server monitoring covers them.

How do I reset weekly boards without dropping views? Use a tumbling window of one week in the view, as leaderboard_daily does per day. Each window is its own board, and the previous week's final standings remain queryable.

Can odds views call a pricing model? Yes. Keep the model as a table keyed by state, as win_probability_model is, or call it from a Python user-defined function inside the view. The streaming layer keeps game state and order flow current; the model supplies the prior.