Multiplayer Game Match History

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5

Problem

We run a multiplayer online game. We need to track every match played, which players participated, their scores, and derive a ranked leaderboard. Product wants to query top players by region, average score per player, and match count over the last 30 days. Design the data model.

Millions of matches. The leaderboard refreshes in fifteen minutes.

Worked solution and explanation

The costume and the trap

Strip the leaderboard costume off and this is a many-to-many between players and matches, plus one question that separates seniors: what do you actually store in the leaderboard? Anyone can draw dim_players and fact_matches. The trap is stashing win_rate on agg_leaderboard to make top-N reads cheap. The moment product wants a regional or seasonal cut, a stored ratio cannot be summed, and every roll-up collapses back into a fact-table rescan. Store the additive parts and compute the rate at read time.

Building the model

Step 1: Bridge players to matches

fact_player_match_results holds one row per player per match with score, kills and is_win. It resolves the many-to-many and is the source of truth for every per-player stat, so its grain is (match_id, player_id).

Step 2: Put an is_ranked flag on the match

fact_matches.is_ranked is the one switch that controls leaderboard eligibility. Leave it out and casual pub-stomps inflate ranks, and there is no clean way to exclude them after the fact.

Step 3: Keep the leaderboard columns additive

agg_leaderboard stores total_wins, total_matches and total_score, never win_rate. Additive columns let you combine two partial aggregates by adding them; a ratio has to be recomputed from totals you no longer kept.

Step 4: Declare the refresh cadence

agg_leaderboard is a table, not a view, refreshed on a fixed cadence (every 15 minutes here). That is the deliberate trade: bounded staleness in exchange for cheap top-N reads, plus a reconciliation path for late corrections.

One defensible model. The bridge fact_player_match_results and the additive pre-aggregate agg_leaderboard are the two load-bearing decisions; everything else is conformed dimensions hanging off the fact.

`agg_leaderboard` stores additive totals; `win_rate` is `total_wins` / `total_matches` at read time
  • dim_players: player_id BIGINT PK, display_name TEXT, region_id INT FK, signup_date DATE
  • dim_regions: region_id INT PK, name TEXT
  • dim_game_modes: mode_id INT PK, name TEXT, team_size INT
  • dim_maps: map_id INT PK, name TEXT, biome TEXT
  • fact_matches: match_id BIGINT PK, mode_id INT FK, map_id INT FK, started_at TIMESTAMP, duration_sec INT, is_ranked BOOLEAN
  • fact_player_match_results: match_id BIGINT FK, player_id BIGINT FK, team TEXT, score INT, kills INT, is_win BOOLEAN
  • agg_leaderboard: player_id BIGINT FK, region_id INT FK, total_matches INT, total_wins INT, total_score BIGINT, refreshed_at TIMESTAMP
  • dim_players.region_id references dim_regions.region_id
  • fact_matches.mode_id references dim_game_modes.mode_id
  • fact_matches.map_id references dim_maps.map_id
  • fact_player_match_results.match_id references fact_matches.match_id
  • fact_player_match_results.player_id references dim_players.player_id
  • agg_leaderboard.player_id references dim_players.player_id
  • agg_leaderboard.region_id references dim_regions.region_id

Reading the leaderboard

Top 10 ranked players by region
SELECT
    p.display_name,
    r.name AS region,
    l.total_wins,
    l.total_matches,
    l.total_wins * 1.0 / NULLIF(l.total_matches, 0) AS win_rate
FROM agg_leaderboard l
JOIN dim_players p ON p.player_id = l.player_id
JOIN dim_regions r ON r.region_id = l.region_id
WHERE l.total_matches >= 50
ORDER BY win_rate DESC, l.total_score DESC
LIMIT 10

win_rate derived from additive columns; NULLIF guards a zero match count

Pre-aggregated leaderboard table
agg_leaderboard refreshed on a schedule. * Cheap top-N reads * Freshness bounded by cadence * Needs a reconciliation job for late is_ranked corrections
Live query over fact tables
Every read scans fact_player_match_results. * Always fresh * Expensive on the hot path * Risk of timeout during peak events

Common follow-up questions