Build a Real-Time Gaming Analytics Pipeline with Just SQL

The gaming industry is evolving fast, with real-time data playing a central role in this transformation. It’s what makes games:

• Immersion: Fast responses and seamless gameplay experiences.
• Fairness: Equal and synchronized updates for all players.
• Engagement: On-the-fly personalization based on user behavior.
• Scalability: Infrastructure that grows alongside the user base.

But building real-time pipelines is often complex and time-consuming.In this post, I’ll walk through how to build a real-time data pipeline for games — cutting development time from weeks to days — focusing on responsive leaderboards and live dashboards.

Real-time leaderboards aren’t just a “nice-to-have”; they drive competition, replay value, and trust. In a fast-paced Battle Royale like Fortnite

Architecture

Player data is ingested into a Kafka topic, and RisingWave consumes this stream to create materialized views for real-time analysis. Using BI tools like Superset or Grafana, we’ll build dashboards to monitor player performance and power leaderboards. Finally, I’ll show how the results from RisingWave can be sent to analytics platforms like BigQuery, Snowflake, or StarRocks and ML models for downstream applications like personalization.

Sample Data

The sample data we’ll ingest into Kafka contains gameplay statistics from a session in a Battle Royale game like Fortnite.

{
  "player_name": "Jordan Smith",
  "mental_state": "tired",
  "match_id": 1,
  "total_kills": 1,
  "damage_dealt": 750,
  "distance_moved": 4764.25,
  "longest_kill": 349.8,
  "time_survived": 302,
  "player_rank": 7,
  "boosts_used": 2,
  "medkits_used": 0,
  "revives": 2,
  "headshot_kills": 3,
  "assists": 3,
  "team_placement": 34,
  "ts": "2024-07-15 21:05:15",
  "placed": 40,
  "eliminations": 3,
  "accuracy": 41.33,
  "hits": 42,
  "damage_taken": 100,
  "damage_to_players": 210,
  "damage_to_structures": 3627,
  "healing_done": 644,
  "items_collected": 31
}

Ingest and Process Data into RisingWave

RisingWave simplifies real-time leaderboard implementation using materialized views and time-windowed rankings with simple SQL-based logic. It handles hundreds of thousands of updates per second with sub-second query latency and reduces development time from weeks to days.

Create a Source

Once you have deployed the RisingWave cluster, either in RisingWave Cloud or an on-premises RisingWave cluster, you can create a source using the following:

CREATE SOURCE fortnite_source (
    player_name VARCHAR,
    mental_state VARCHAR,
    match_id INT,
    total_kills INT,
    damage_dealt INT,
    distance_moved DOUBLE PRECISION,
    longest_kill DOUBLE PRECISION,
    time_survived INT,
    player_rank INT,
    boosts_used INT,
    medkits_used INT,
    revives INT,
    headshot_kills INT,
    assists INT,
    team_placement INT,
    ts TIMESTAMP,
    placed INT,
    eliminations INT,
    accuracy DOUBLE PRECISION,
    hits INT,
    damage_taken INT,
    damage_to_players INT,
    damage_to_structures DOUBLE PRECISION,
    healing_done INT,
    items_collected INT
)
WITH(
connector='kafka',
topic ='fortnite_topic',
properties.bootstrap.server ='xxxxx:9092',
properties.sasl.mechanism = 'SCRAM-SHA-256',
properties.security.protocol = 'SASL_SSL',
properties.sasl.username = 'xxxxxxx',
properties.sasl.password = 'xxxxxxx',
scan.startup.mode ='earliest'
)FORMAT PLAIN ENCODE JSON;

Player Performance Aggregation in a 1-Minute Window

This query calculates each player’s average damage, average kills, and total matches played within 1-minute intervals from the fortnite_source table, grouping by player name and time window.

select
  player_name,
  AVG(damage_dealt) AS avg_damage_dealt,
  AVG(total_kills) AS avg_kills,
  COUNT(match_id) AS matches_played,
  window_start, window_end
FROM TUMBLE (fortnite_source, ts, INTERVAL '1 minute')
GROUP BY player_name,window_start, window_end;

Top 3 Players by Kills in a 1-Minute Window

This query identifies the top 3 players with the highest kills in each 1-minute window from the fortnite_source table. It first sums the total kills per player within each window, ranks the players by their kills within each window, and then selects the top 3 ranked players for each window, ordering the results by window start time.

WITH PlayerKillsPerWindow AS (
    SELECT
        player_name,
        window_start,
        window_end,
        SUM(total_kills) AS total_kills
    FROM
        TUMBLE (fortnite_source, ts, INTERVAL '1 minute')
    GROUP BY
        player_name,
        window_start,
        window_end
),
RankedPlayerKills AS (
    SELECT
        player_name,
        window_start,
        window_end,
        total_kills,
        ROW_NUMBER() OVER (PARTITION BY window_start, window_end ORDER BY total_kills DESC) AS rank
    FROM
        PlayerKillsPerWindow
)
SELECT
    player_name,
    total_kills,
    window_start,
    window_end
FROM
    RankedPlayerKills
WHERE
    rank <= 3
order by window_start;

Top 3 Players by Average Survival Time in a 1-Minute Window

This query identifies the top 3 players with the highest average survival time in each 1-minute window from the fortnite_source table. It calculates the average survival time per player within each window, ranks the players by their average survival time within each window, and then selects the top 3 ranked players for each window, ordering the results by window start time.

WITH PlayerSurvivalTimePerWindow AS (
    SELECT
        player_name,
        window_start,
        window_end,
        AVG(time_survived) AS avg_survival_time
    FROM
        TUMBLE (fortnite_source, ts, INTERVAL '1 minute')
    GROUP BY
        player_name,
        window_start,
        window_end
),
RankedPlayerSurvivalTime AS (
    SELECT
        player_name,
        avg_survival_time,
        window_start,
        window_end,
        ROW_NUMBER() OVER (PARTITION BY window_start, window_end ORDER BY avg_survival_time DESC) AS rank
    FROM
        PlayerSurvivalTimePerWindow
)
SELECT
    player_name,
    avg_survival_time,
    window_start,
    window_end
FROM
    RankedPlayerSurvivalTime
WHERE
    rank <= 3
ORDER BY
    window_start;

Top 3 Players by Accuracy in a 1-Minute Window

This query identifies the top 3 players with the highest accuracy in each 1-minute window from the fortnite_source table. It retrieves player accuracy within each window, ranks players by that accuracy, and then selects the top 3 for each window, ordering the results by window start time.

WITH PlayerAccuracyPercentagePerWindow AS (
    SELECT
        player_name,
        window_start,
        window_end,
        accuracy
    FROM
        TUMBLE (fortnite_source, ts, INTERVAL '1 minute')
    GROUP BY
        player_name,
        window_start,
        window_end
),
RankedPlayerAccuracyPercentage AS (
    SELECT
        player_name,
        window_start,
        window_end,
        accuracy,
        ROW_NUMBER() OVER (PARTITION BY window_start, window_end ORDER BY accuracy DESC) AS rank
    FROM
        PlayerAccuracyPercentagePerWindow
)
SELECT
    player_name,
    accuracy,
    window_start,
    window_end
FROM
    RankedPlayerAccuracyPercentage
WHERE
    rank <= 3
ORDER BY
    window_start;

Visualize Real-Time Data with Superset or Grafana

After building real-time materialized views in RisingWave, the next step is visualization. You can connect RisingWave as a data source to Apache Superset or Grafana, and use it to build interactive dashboards from the tables and views.

Once connected, you can add materialized views as datasets to build tables, charts, and unified real-time dashboards, allowing you to monitor game metrics, leaderboards, and trends visually and instantly.

Integrating Real-Time Game Analytics and Machine Learning

In modern gaming architectures, real-time leaderboards are not standalone — they often feed into downstream analytics platforms and AI/ML pipelines to further enhance player experience.

• Analytics Platforms: Tools like BigQuery, Snowflake, or StarRocks are commonly used to analyze player behavior, monitor KPIs, and detect gameplay trends in real time.
• AI/ML Pipelines: Streaming data can be used to power intelligent models for: In-game personalization and recommendations; Dynamic difficulty adjustment; Predictive behavior modeling.

Strategic Benefit: The fusion of real-time data with machine learning enables adaptive and personalized game experiences that evolve based on player actions.

Conclusion

We’ve walked through how to build a real-time data pipeline for games using Kafka and SQL — powering responsive leaderboards and live dashboards without complex streaming code. Player events flow into Kafka, are aggregated with materialized views and window functions, and become instantly queryable for monitoring and ranking.

The key takeaway: you don’t need weeks of custom streaming code to deliver millisecond updates. A SQL-first pipeline makes it possible to ship faster, keep players engaged, and extend the same pattern to analytics platforms or ML models for personalization and smarter game experiences.