Build a multiplayer game backend. Track player stats, maintain real-time leaderboards, record match history, and manage in-game inventories.

Note
 This tutorial assumes you have a running 3-node Ferrosa cluster. If you haven’t set one up yet, follow the 3-Node Cluster Setup guide first — it only takes about 5 minutes.

Create the Keyspace

Gaming backends need low latency above all else. When a player finishes a match, they want to see the leaderboard update immediately — not wait for a cache refresh. Let’s create a keyspace with full replication.

With RF=3, player profiles and leaderboards survive any single node failure. Players stay in the game even during maintenance windows.

Design the Schema

A multiplayer game backend needs to answer four questions fast: Who is this player? What happened in a match? How did this player do over time? Who is at the top of the leaderboard? One table for each.

Players

The player profile: identity, progression, and lifetime stats. This table gets read every time a player logs in or another player inspects their profile.

Matches

The global match log. We partition by game_mode and cluster by started_at descending so you can efficiently pull up the most recent matches in each mode.

Player Matches

Each player’s personal match history with their individual performance stats. This is the "Match History" tab on a player’s profile.

Leaderboard

The ranked leaderboard for each game mode. Pre-sorted by rank so the "Top 10" query is a simple range read with no sorting required.

Tip
 We denormalize the username into the leaderboard table. This means we can render the entire leaderboard in a single query without joining to the players table. If a player changes their display name, you update it in both places — a small price for fast reads.

Four tables, each tailored for a specific game screen.

The full schema (keyspace and tables):

CREATE KEYSPACE IF NOT EXISTS gaming
  WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
  };

USE gaming;

CREATE TABLE players (
    player_id         uuid PRIMARY KEY,
    username          text,
    display_name      text,
    level             int,
    xp                bigint,
    wins              int,
    losses            int,
    kill_death_ratio  float,
    created_at        timestamp,
    last_login        timestamp
);

CREATE TABLE matches (
    game_mode         text,
    started_at        timestamp,
    match_id          uuid,
    map_name          text,
    duration_seconds  int,
    winner_id         uuid,
    players           list<text>,
    PRIMARY KEY (game_mode, started_at, match_id)
) WITH CLUSTERING ORDER BY (started_at DESC, match_id ASC);

CREATE TABLE player_matches (
    player_id    uuid,
    match_time   timestamp,
    match_id     uuid,
    kills        int,
    deaths       int,
    assists      int,
    score        int,
    result       text,       -- win, loss, draw
    PRIMARY KEY (player_id, match_time, match_id)
) WITH CLUSTERING ORDER BY (match_time DESC, match_id ASC);

CREATE TABLE leaderboard (
    game_mode   text,
    rank        int,
    player_id   uuid,
    username    text,
    score       bigint,
    PRIMARY KEY (game_mode, rank)
);

Register Players

Let’s create five players at various skill levels. In a real game, these profiles would be created during account registration and updated after every match.

PixelQueen is the veteran here — level 55 with a 2.34 K/D ratio. Ironclad is still working their way up. A well-designed game backend makes every player’s journey visible and motivating.

Record Matches

Let’s record ten matches across two game modes. The players list stores usernames for quick display, and winner_id records who took the victory.

Ten matches — five ranked, five casual. PixelQueen and StarBreaker are dominating tonight. Notice how the list type stores the player roster for each match.

Track Per-Player Match Stats

Every player gets their own match history with individual performance stats. This is the data behind the "Match History" screen.

Twenty player-match records across five players. PixelQueen’s 28-kill game at 19:00 stands out — that is the kind of performance that puts you at the top of the leaderboard.

Build the Leaderboard

The leaderboard is a materialized view of player rankings. In production, a game server would recompute rankings after each match and update this table. For now, let’s insert the current standings directly.

PixelQueen leads ranked, but StarBreaker takes the top spot in casual.

All data (players, matches, player match stats, and leaderboard):

USE gaming;

-- Player profiles
INSERT INTO players (player_id, username, display_name, level, xp, wins, losses, kill_death_ratio, created_at, last_login)
VALUES (a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, 'shadowfox', 'ShadowFox', 42, 185000, 312, 198, 1.85, '2025-01-15 10:00:00', '2026-03-17 20:30:00');

INSERT INTO players (player_id, username, display_name, level, xp, wins, losses, kill_death_ratio, created_at, last_login)
VALUES (b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2, 'neonknight', 'NeonKnight', 38, 156000, 278, 222, 1.52, '2025-03-08 14:30:00', '2026-03-17 22:15:00');

INSERT INTO players (player_id, username, display_name, level, xp, wins, losses, kill_death_ratio, created_at, last_login)
VALUES (c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, 'pixelqueen', 'PixelQueen', 55, 274000, 445, 155, 2.34, '2024-11-20 09:00:00', '2026-03-18 01:00:00');

INSERT INTO players (player_id, username, display_name, level, xp, wins, losses, kill_death_ratio, created_at, last_login)
VALUES (d4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4, 'ironclad', 'Ironclad', 29, 98000, 189, 211, 1.12, '2025-06-01 16:00:00', '2026-03-16 18:45:00');

INSERT INTO players (player_id, username, display_name, level, xp, wins, losses, kill_death_ratio, created_at, last_login)
VALUES (e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, 'starbreaker', 'StarBreaker', 47, 210000, 368, 182, 2.01, '2025-02-14 11:00:00', '2026-03-17 23:30:00');

-- Ranked matches
INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('ranked', '2026-03-17 19:00:00', uuid(), 'Crimson Fortress', 1245, c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, ['shadowfox', 'neonknight', 'pixelqueen', 'ironclad']);

INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('ranked', '2026-03-17 19:30:00', uuid(), 'Neon District', 980, a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, ['shadowfox', 'starbreaker', 'ironclad']);

INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('ranked', '2026-03-17 20:15:00', uuid(), 'Skyfall Arena', 1560, e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, ['pixelqueen', 'starbreaker', 'neonknight', 'shadowfox']);

INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('ranked', '2026-03-17 21:00:00', uuid(), 'Crimson Fortress', 1100, c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, ['pixelqueen', 'ironclad', 'starbreaker']);

INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('ranked', '2026-03-17 22:00:00', uuid(), 'Neon District', 890, e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, ['shadowfox', 'neonknight', 'starbreaker']);

-- Casual matches
INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('casual', '2026-03-17 18:30:00', uuid(), 'Sunset Bay', 720, b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2, ['neonknight', 'ironclad']);

INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('casual', '2026-03-17 19:15:00', uuid(), 'Crystal Caves', 540, d4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4, ['ironclad', 'shadowfox', 'neonknight']);

INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('casual', '2026-03-17 20:00:00', uuid(), 'Sunset Bay', 660, a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, ['shadowfox', 'pixelqueen']);

INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('casual', '2026-03-17 21:30:00', uuid(), 'Crystal Caves', 480, e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, ['starbreaker', 'neonknight', 'ironclad']);

INSERT INTO matches (game_mode, started_at, match_id, map_name, duration_seconds, winner_id, players)
VALUES ('casual', '2026-03-17 22:30:00', uuid(), 'Sunset Bay', 600, c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, ['pixelqueen', 'starbreaker']);

-- ShadowFox's matches
INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, '2026-03-17 19:00:00', uuid(), 18, 12, 7, 2150, 'loss');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, '2026-03-17 19:30:00', uuid(), 24, 8, 5, 3200, 'win');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, '2026-03-17 20:00:00', uuid(), 15, 6, 9, 2400, 'win');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, '2026-03-17 20:15:00', uuid(), 20, 14, 6, 2600, 'loss');

-- NeonKnight's matches
INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2, '2026-03-17 18:30:00', uuid(), 14, 9, 3, 1850, 'win');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2, '2026-03-17 19:00:00', uuid(), 12, 15, 8, 1600, 'loss');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2, '2026-03-17 19:15:00', uuid(), 8, 11, 4, 1200, 'loss');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2, '2026-03-17 22:00:00', uuid(), 16, 10, 5, 2050, 'loss');

-- PixelQueen's matches
INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, '2026-03-17 19:00:00', uuid(), 28, 7, 4, 3800, 'win');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, '2026-03-17 20:00:00', uuid(), 10, 8, 6, 1800, 'loss');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, '2026-03-17 21:00:00', uuid(), 25, 9, 3, 3500, 'win');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, '2026-03-17 22:30:00', uuid(), 22, 5, 7, 3400, 'win');

-- Ironclad's matches
INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (d4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4, '2026-03-17 18:30:00', uuid(), 9, 14, 6, 1100, 'loss');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (d4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4, '2026-03-17 19:00:00', uuid(), 11, 16, 5, 1350, 'loss');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (d4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4, '2026-03-17 19:15:00', uuid(), 16, 10, 8, 2100, 'win');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (d4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4, '2026-03-17 21:00:00', uuid(), 13, 18, 4, 1500, 'loss');

-- StarBreaker's matches
INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, '2026-03-17 19:30:00', uuid(), 19, 11, 6, 2500, 'loss');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, '2026-03-17 20:15:00', uuid(), 26, 10, 4, 3300, 'win');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, '2026-03-17 21:30:00', uuid(), 21, 7, 9, 3100, 'win');

INSERT INTO player_matches (player_id, match_time, match_id, kills, deaths, assists, score, result)
VALUES (e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, '2026-03-17 22:00:00', uuid(), 22, 9, 5, 2900, 'win');

-- Ranked mode leaderboard
INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('ranked', 1, c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, 'pixelqueen', 274000);

INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('ranked', 2, e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, 'starbreaker', 210000);

INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('ranked', 3, a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, 'shadowfox', 185000);

INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('ranked', 4, b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2, 'neonknight', 156000);

INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('ranked', 5, d4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4, 'ironclad', 98000);

-- Casual mode leaderboard
INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('casual', 1, e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5, 'starbreaker', 142000);

INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('casual', 2, c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3, 'pixelqueen', 138000);

INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('casual', 3, a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1, 'shadowfox', 115000);

INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('casual', 4, d4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4, 'ironclad', 67000);

INSERT INTO leaderboard (game_mode, rank, player_id, username, score)
VALUES ('casual', 5, b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2, 'neonknight', 62000);

Run Game Queries

Here are the queries that power the different screens in your game client.

Player Profile

The first thing a player sees when they open the game. One partition read, sub-millisecond response.

Match History

A player’s recent performance. The descending clustering order means the most recent matches appear first.

Leaderboard Top 10

The query everyone cares about. Because rank is the clustering key, this is a simple range read — no sorting needed.

Recent Matches by Game Mode

Show what is happening in the game right now. Great for a "Live Matches" or "Recent Activity" feed.

Player Stats Overview

Calculate a quick win rate from the profile data. This is the kind of stat that appears on loading screens and profile cards.

Update Stats After a Match

After each match, the game server updates the player’s profile. Here is what that looks like for a player who just won.

Important
 In production, you would also update the leaderboard table whenever a player’s score changes enough to affect rankings. Some games recompute the full leaderboard periodically (every few minutes), while others update it in real time after each match. The best approach depends on your game’s scale and how competitive the ranking needs to feel.

All queries (selects and updates):

USE gaming;

-- Look up PixelQueen's profile
SELECT display_name, level, xp, wins, losses, kill_death_ratio, last_login
FROM players
WHERE player_id = c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3;

-- ShadowFox's last 5 matches
SELECT match_time, kills, deaths, assists, score, result
FROM player_matches
WHERE player_id = a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1
LIMIT 5;

-- Top 10 players in ranked mode
SELECT rank, username, score
FROM leaderboard
WHERE game_mode = 'ranked'
LIMIT 10;

-- Last 5 ranked matches
SELECT started_at, map_name, duration_seconds, players
FROM matches
WHERE game_mode = 'ranked'
LIMIT 5;

-- StarBreaker's win/loss record
SELECT display_name, wins, losses, kill_death_ratio
FROM players
WHERE player_id = e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5;

-- PixelQueen won another match: increment wins and XP
UPDATE players
SET wins = 446,
    xp = 277400,
    kill_death_ratio = 2.36,
    last_login = '2026-03-18 01:15:00'
WHERE player_id = c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3;

What You Learned

You just built the data layer for a multiplayer game. Here is what you accomplished:

  • Designed a player profile table for sub-millisecond identity lookups

  • Built a match history table with per-player performance tracking using descending time order

  • Created a pre-ranked leaderboard partitioned by game mode for instant top-10 queries

  • Used CQL collection types (lists) to store variable-length player rosters in match records

  • Modeled denormalized data across tables to serve each game screen with a single partition read

  • Wrote update queries that a game server would run after each match to keep stats current

This architecture is used by real game studios. The key principle is the same one that runs through every Ferrosa tutorial: design your tables around your queries, not the other way around. Each screen in the game client maps to exactly one table and one partition read. That is how you keep latency low even with millions of concurrent players.