---
title: Build a real-time game leaderboard
tags: gaming-analytics
meta:
  description: Learn how to build a real-time leaderboard using Tinybird.
tag: gaming-analytics
---

# Build a real-time game leaderboard

Read on to learn how to build a real-time leaderboard using Tinybird. 

Leaderboards are a visual representation that ranks things by one or more attributes. For gaming use cases, commonly-displayed attributes include total points scored, high game scores, and number of games played.

Leaderboards are used for far more than games. For example, app developers use leaderboards to display miles biked, donations raised, documentation pages visited most often, and countless other examples - basically, anywhere there is some user attribute that can be ranked to compare results. This tutorial is a great starting point for building your own leaderboard.

{% button
    icon="github"
    variant="ghost"
    href="https://github.com/tinybirdco/demo-user-facing-leaderboard"
    children="GitHub Repository"
/%}

{% image src="/img/leaderboard-tutorial-2.png" alt="A fast, fun leaderboard built on Tinybird" /%}

The tutorial consists of the following steps:

1. Generate a mock game event stream that mimics a high-intensity Flappybird global tournament.
2. Post these mock events to your Tinybird Workspace using the Events API.
3. Transform (rank) this data using Tinybird Pipes and SQL.
4. Optimize your data handling with a Materialized View.
5. Publish the results as a Tinybird API Endpoint.
6. Generate a leaderboard that makes calls to your API Endpoint securely and directly from the browser.

Each time a leaderboard request is made, up-to-the-second results are returned for the leaderboard app to render. When embedded in the game, a `leaderboard` API Endpoint is requested when a game ends. The app makes requests on a specified interval and has a button for ad-hoc requests.

Game events consist of three values:

- `core` - Generated when a point is scored.
- `game_over` - Generated when a game ends.
- `purchase` - Generated when a 'make-the-game-easier' coupon is redeemed.

Each event object has the following JSON structure:

```json {% title="Example JSON event object" %}
{
    "session_id": "1f2c8bcf-8a5b-4eb1-90bf-8726e63d81b7",
    "name": "Marley",
    "timestamp": "2024-06-20T19:06:15.373Z",
    "type": "game_over",
    "event": "Mockingbird"
}
```

Here's how it all fits together:

{% image src="/img/leaderboard-tutorial-1.png" alt="Events and process of the leaderboard" /%}

## Prerequisites

To complete this tutorial, you need the following:

1. A [free Tinybird account](https://www.tinybird.co/signup)
2. An empty Tinybird Workspace
3. Node.js 20.11 or higher
4. Python 3.8 or higher

{% steps %}

## Create a Tinybird Workspace

Go to ([app.tinybird.co](https://app.tinybird.co)) and create an empty Tinybird Workspace called `tiny_leaderboard` in your preferred region.

## Create a Data Source for events

You can create a Data Source based on a schema that you define or rely on the [Mockingbird](https://mockingbird.tinybird.co/docs) tool used to stream mock data to create the Data Source for you. While the Mockingbird method is faster, building your own Data Source gives you more control and introduces some fundamental concepts along the way.

In the Tinybird UI, add a new Data Source and use the `Write schema` option. In the schema editor, use the [following schema](https://github.com/tinybirdco/demo-user-facing-leaderboard/blob/main/tinybird/datasources/game_events.datasource):

```tb {% title="Data Source schema" %}
SCHEMA >
    `name` String `json:$.name`,
    `session_id` String `json:$.session_id`,
    `timestamp` DateTime64(3) `json:$.timestamp`,
    `type` LowCardinality(String) `json:$.type`,
    `event` String `json:$.event`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYear(timestamp)"
ENGINE_SORTING_KEY "event, name, timestamp"
```

Name the Data Source `game_events` and select **Create Data Source**.

This schema definition shows how the incoming JSON events are parsed and assigned to each of schema fields. The definition also defines [database table ‘engine’ details](/classic/get-data-in/data-sources#supported-engines-settings). Tinybird projects are made of Data Source and Pipe definition files like this example, and they can be managed like any other code project using Git.

## Create a mock data stream

In a real-life scenario, you'd stream your game events into the `game_events` Data Source. For this tutorial, you use [Mockingbird](https://mockingbird.tinybird.co/docs), an open source mock data stream generator, to stream mock events instead. Mockingbird generates a JSON payload based on a predefined schema and posts it to the [Tinybird Events API](/classic/get-data-in/ingest-apis/events-api), which then writes the data to your Data Source.

Use [this Mockingbird link](https://mockingbird.tinybird.co/?host=eu_gcp&datasource=game_events&eps=10&withLimit=on&generator=Tinybird&endpoint=eu_gcp&limit=-1&generatorName=Tinybird&template=Flappybird&schema=Preset) to generate fake data for the `game_events` Data Source. Using the link provides a preconfigured schema.

Enter your Workspace admin Token and select the Host region that matches your Workspace region. Select **Save**, then scroll down and select **Start Generating**.

{% snippet title="api-region-reminder" /%}

In the Tinybird UI, confirm that the `game_events` Data Source is successfully receiving data.

Leaderboards typically leverage a concise data schema with just a user/item name, the ranked attribute, and a timestamp. This tutorial is based on this schema:

- `name` String
- `session_id` String
- `timestamp` DateTime64(3)
- `type` LowCardinality(String)
- `event` String

Ranking algorithms can be based on a single score, time-based metrics, or weighted combinations of factors.

## Transform and publish your data

Your Data Source is collecting events, so now it's time to create some [Pipes](/classic/work-with-data/query/pipes). Pipes are made up of chained, reusable SQL [nodes](/classic/work-with-data/query/pipes#nodes) and form the logic that ranks the results.

Start by creating a `leaderboard` Pipe with two nodes. The first node returns all 'score' events. The second node takes those results and counts these events by player and session (which defines a single game), and returns the top 10 results.

In the Tinybird UI, create a new Pipe called `leaderboard`. Paste in the following SQL and rename the first node `get_all_scores`:

```sql {% title="get_all_scores Node" %}
%
SELECT name AS player_id, timestamp, session_id, event
FROM game_events
WHERE
    type = 'score'
    AND event == {{ String(event_param, 'Mockingbird', description="Event to filter on") }}
```

This query returns all events where the type is `score`.

This node creates a query parameter named `event_param` using the [Tinybird templating syntax](/classic/work-with-data/query-parameters).  This instance of Flappybird supports an ‘event’ attribute that supports organizing players, games, and events into separate groups. As shown previously, incoming Mockingbird game events have a `"event": "Mockingbird"` attribute.

Select **Run** and add a new node underneath, called `endpoint`. Paste in:

```sql {% title="endpoint Node" %}
SELECT player_id, 
  session_id, 
  event,
  count() AS score
FROM get_all_scores
GROUP BY player_id, session_id, event
ORDER BY score DESC
LIMIT 10
```

Select **Run**, then select **Create API Endpoint**. Your data is now ranked, published, and available for consuming.

## Optimize with Materialized Views

Before you run the frontend for your leaderboard, there are a few optimizations to make. Even with small datasets, it's a great habit to get into.

[Materialized Views](/classic/work-with-data/process-and-copy/materialized-views) are updated as data is ingested, and create intermediate states that are merged with already-processed data. The Materialized View (MV) continuously re-evaluates queries as new events are inserted, reducing both latency and processed-data-per-query. In this case, the MV you create precalculates the top scores, and merges those with recently-received events. This significantly improves query performance by reducing the amount of data that needs to be processed for each leaderboard request.

To create a new Materialized View, begin by adding a new Pipe and call it `user_stats_mv`. Then paste the following SQL into the first Node:

```sql
SELECT
    event,
    name AS player_id,
    session_id,
    countIfState(type = 'score') AS scores,
    countIfState(type = 'game_over') AS games,
    countIfState(type = 'purchase') AS purchases,
    minState(timestamp) AS start_ts,
    maxState(timestamp) AS end_ts
FROM games_events
GROUP BY
    event,
    player_id,
    session_id
```

This query relies on the `countIfState` function, which includes the `-State` operator to maintain immediate states containing recent data. When triggered by a `-Merge` operator, these intermediate states are combined with the precalculated data. The `countIfState` function is used to maintain counts of each type of game event.

Name this node `populate_mv`, then [publish it as a Materialized View](/classic/work-with-data/process-and-copy/materialized-views). Name your Materialized View `user_stats`.

You now have a new Data Source called `user_stats`, which is a Materialized View that is continuously updated with the latest game events. The `-State` modifier that maintains intermediate states as new data arrives is paired with a `-Merge` modifier in Pipes that pull from the `user_stats` Data Source.

## Update leaderboard Pipe

Now that `user_stats` is available, rebuild the `leaderboard` Pipe to take advantage of this more efficient Data Source. This step helps prepare your leaderboard feature to handle massive amounts of game events while serving requests to thousands of users.

The updated leaderboard Pipe consists of three nodes:

- `rank_games` - Applies the countMerge(scores) function to get the current total from the user_stats Data Source.
- `last_game` - Retrieves the score from the player's most recent game and determines the player's rank.
- `endpoint` - Combines the results of these two nodes and ranks by score.

The `last_game` node introduces the user-facing aspect of the leaderboard. This node retrieves a specific user's data and blends it into the leaderboard results.

To get started, update the `leaderboard` Pipe to use the `user_stats` Materialized View.

Return to the `leaderboard` Pipe and un-publish it.

Now, change the name of the first node to `rank_games` and update the SQL to:

```sql {% title="rank_games Node" %}
%
SELECT
    ROW_NUMBER() OVER (ORDER BY total_score DESC, t) AS rank,
    player_id,
    session_id,
    countMerge(scores) AS total_score,
    maxMerge(end_ts) AS t
FROM user_stats
GROUP BY player_id, session_id
ORDER BY rank
```

A few things to notice:

1. The `rank_games` node now uses the `user_stats` Materialized View instead of the `game_events` Data Source.
2. The use of the `countMerge(scores)` function. The `-Merge` operator triggers the MV-based `user_stats` Data Source to combine any intermediate states with the pre-calculated data and return the results.
3. The use of the `ROW_NUMBER()` window function that returns a ranking of top scores. These rankings are based on the merged scores (aliased as `total_scores`) retrieved from the `user_stats` Data Source.

Next, change the name of the second node to `last_game` and update the SQL to:

```sql {% title="last_game Node" %}
%
SELECT
    argMax(rank, t) AS rank,
    player_id,
    argMax(session_id, t) AS session_id,
    argMax(total_score, t) AS total_score
FROM rank_games
WHERE
    player_id = {{ String(player_id, 'Jim', description="Player to filter on", required=True) }}
GROUP BY player_id
```

This query returns the highest rank of a specified player and introduces a `player_id` query parameter.

To combine these results, add a new node called `endpoint` and paste the following SQL:

```sql  {% title="endpoint Node" %}
SELECT *
FROM
    (
        SELECT rank, player_id, session_id, total_score
        FROM rank_games
        WHERE (player_id, session_id) NOT IN (SELECT player_id, session_id FROM last_game)
        LIMIT 10
        UNION ALL
        SELECT rank, player_id, session_id, total_score
        FROM last_game
    )
ORDER BY rank ASC
```

This query applies the `UNION ALL` statement to combine the two result sets. The selected attribute data types must match to be combined.

This completes the `leaderboard` Pipe. Publish it as an API Endpoint.

Now that the final version of the 'leaderboard' Endpoint has been published, create one last Pipe in the UI. This one gets the overall stats for the leaderboard, the number of players and completed games. Name the Pipe `get_stats` and create a single node named `endpoint`:

```sql  {% title="endpoint node in the get_stats Pipe" %}
WITH player_count AS (
  SELECT COUNT(DISTINCT player_id) AS players
  FROM user_stats
),
game_count AS (
  SELECT COUNT(*) AS games
  FROM game_events
  WHERE type == 'game_over'
)
SELECT players, games
FROM player_count, game_count
```

Publish this node as an API Endpoint. You're ready to get it all running!

## Run your app

Clone the [`demo-user-facing-leaderboard` repo](https://github.com/tinybirdco/demo-user-facing-leaderboard) locally.

Install the app dependencies by running this command from the `app` dir of the cloned repo:

```shell
npm install
```

### Add your Tinybird settings as environment variables

Create a new `.env.local` file:

```shell
touch .env.local
```

Copy your Tinybird Admin Token, Workspace UUID, obtained from **Workspace** > **Settings** > **Advanced settings** > **`...`**, and API host URL from your Tinybird Workspace into the new `.env.local`:

```shell
TINYBIRD_SIGNING_TOKEN="YOUR SIGNING TOKEN" # Use your Admin Token as the signing token
TINYBIRD_WORKSPACE="YOUR WORKSPACE ID" # The UUID of your Workspace
NEXT_PUBLIC_TINYBIRD_HOST="YOUR TINYBIRD API HOST e.g. https://api.tinybird.co" # Your regional API host
```

### Run your app {% id="run-your-app-2" %}

Run your app locally and navigate to `http://localhost:3000`:

```shell
npm run dev
```

You now have an optimized gaming leaderboard ingesting real-time data! Have a think about how you'd adapt or extend it for your own use case.

{% /steps %}

## Next steps

- Read the in-depth blog post on [building a real-time leaderboard](https://www.tinybird.co/blog-posts/building-real-time-leaderboards-with-tinybird).
- Understand today's real-time analytics landscape with [Tinybird's definitive guide](https://www.tinybird.co/blog-posts/real-time-analytics-a-definitive-guide).
- Learn how to implement [multi-tenant security](https://www.tinybird.co/blog-posts/multi-tenant-saas-options) in your user-facing analytics.