# Ultra Fast Gaming: Firebolt Sample Dataset (/free-sample-datasets/ultra-fast-gaming)



UltraFast Gaming Inc. publishes online games across PlayStation, Xbox, PC, iOS, and Nintendo. The
company collects extensive data about games, levels, players, tournaments, play sessions, and
rankings to support data-driven decisions about development, tournaments, community initiatives, and
interactive leaderboards.

## Getting started [#getting-started]

### Create and select an engine [#create-and-select-an-engine]

Create a database engine before running queries — through the Firebolt UI or with SQL:

```sql
CREATE ENGINE "ultra_fast_engine" WITH
    TYPE = "S"
    NODES = 1
    AUTO_STOP = 10
    INITIALLY_STOPPED = false
    AUTO_START = true
    CLUSTERS = 1;
```

After creating the engine, select it using the dropdown in the query editor.

### Load the dataset [#load-the-dataset]

For new Firebolt accounts, the UltraFast Gaming dataset loads automatically into the `ultra_fast`
database. Access it with:

```sql
USE ultra_fast;
```

For existing accounts without the dataset, create the database and ingest the data:

```sql
CREATE DATABASE ultra_fast;

COPY INTO games FROM 's3://firebolt-sample-datasets-public-us-east-1/gaming/parquet/games/'
WITH PATTERN='*.snappy.parquet' TYPE = PARQUET;

COPY INTO levels FROM 's3://firebolt-sample-datasets-public-us-east-1/gaming/parquet/levels/'
WITH PATTERN='*.snappy.parquet' TYPE = PARQUET;

COPY INTO players FROM 's3://firebolt-sample-datasets-public-us-east-1/gaming/parquet/players/'
WITH PATTERN='*.snappy.parquet' TYPE = PARQUET;

COPY INTO playstats FROM 's3://firebolt-sample-datasets-public-us-east-1/gaming/parquet/playstats/'
WITH PATTERN='*.snappy.parquet' TYPE = PARQUET;

COPY INTO rankings FROM 's3://firebolt-sample-datasets-public-us-east-1/gaming/parquet/rankings/'
WITH PATTERN='*.snappy.parquet' TYPE = PARQUET;

COPY INTO tournaments FROM 's3://firebolt-sample-datasets-public-us-east-1/gaming/parquet/tournaments/'
WITH PATTERN='*.snappy.parquet' TYPE = PARQUET;

SHOW TABLES;
```

## Tables included [#tables-included]

| Table         | Description                                     |
| ------------- | ----------------------------------------------- |
| `Games`       | Game titles, supported platforms, launch dates  |
| `Levels`      | Game level information                          |
| `Players`     | Player profiles, platforms, subscription status |
| `PlayStats`   | Session statistics, scores, playtime metrics    |
| `Rankings`    | Tournament rankings and player positions        |
| `Tournaments` | Tournament details and metadata                 |

## Example queries [#example-queries]

### Platform support analysis [#platform-support-analysis]

Determine which games support a specific platform:

```sql
SELECT
    Title AS GameTitle,
    ARRAY_CONTAINS(SupportedPlatforms, 'PlayStation') AS SupportsPlayStation
FROM
    Games;
```

Find the total number of platforms a game supports:

```sql
SELECT
    Title AS GameTitle,
    ARRAY_LENGTH(SupportedPlatforms) AS NumberOfPlatforms
FROM
    Games
WHERE
    Title = 'Johnny B. Quick';
```

Functions used: `ARRAY_CONTAINS`, `ARRAY_LENGTH`.

### Game launch date analysis [#game-launch-date-analysis]

Analyze launch dates, time elapsed, and recency:

```sql
SELECT
    Title AS GameTitle,
    DATE_DIFF('day', LaunchDate, CURRENT_DATE) AS DaysSinceLaunch,
    DATE_TRUNC('month', LaunchDate) AS LaunchMonth,
    CASE
        WHEN LaunchDate >= CURRENT_DATE - INTERVAL '1 year' THEN 'Yes'
        ELSE 'No'
    END AS LaunchedWithinLastYear
FROM
    Games;
```

Functions used: `DATE_DIFF`, `DATE_TRUNC`, and date arithmetic operators.

### Tournament player performance [#tournament-player-performance]

Use window functions to rank player performance within specific tournaments:

```sql
WITH PlayerScores AS (
    SELECT
        ps.GameID,
        ps.PlayerID,
        AVG(ps.CurrentScore) AS AvgScore
    FROM
        PlayStats ps
    WHERE
        ps.TournamentID IN (56, 16, 98)
    GROUP BY ALL
),
RankedScores AS (
    SELECT
        ps.GameID,
        ps.PlayerID,
        ps.AvgScore,
        RANK() OVER (PARTITION BY ps.GameID ORDER BY ps.AvgScore DESC) AS ScoreRank
    FROM
        PlayerScores ps
)
SELECT
    g.Title AS GameTitle,
    p.Nickname AS PlayerNickname,
    rs.AvgScore,
    rs.ScoreRank
FROM
    RankedScores rs
JOIN
    Games g ON rs.GameID = g.GameID
JOIN
    Players p ON rs.PlayerID = p.PlayerID
WHERE
    rs.ScoreRank <= 50;
```

### Subscription and platform impact on playtime [#subscription-and-platform-impact-on-playtime]

Analyze how subscription status and platform influence player engagement:

```sql
WITH PlayerPlayTime AS (
    SELECT
        ps.PlayerID,
        ps.GameID,
        p.IsSubscribedToNewsletter,
        UNNEST(p.Platforms) AS Platform,
        AVG(ps.CurrentPlayTime) AS AvgPlayTime,
        RANK() OVER (PARTITION BY ps.GameID ORDER BY AVG(ps.CurrentPlayTime) DESC) AS PlayTimeRank
    FROM
        PlayStats ps
    JOIN
        Players p ON ps.PlayerID = p.PlayerID
    WHERE
        ps.StatTime BETWEEN '2020-12-01' AND '2021-02-01'
        AND ps.TournamentID > 100
    GROUP BY ALL
),
FilteredPlayers AS (
    SELECT
        ppt.PlayerID,
        ppt.GameID,
        ppt.IsSubscribedToNewsletter,
        ppt.Platform,
        ppt.AvgPlayTime,
        ppt.PlayTimeRank,
        CASE
            WHEN ppt.IsSubscribedToNewsletter = TRUE THEN 'Subscribed'
            ELSE 'Not Subscribed'
        END AS SubscriptionStatus
    FROM
        PlayerPlayTime ppt
    WHERE
        ppt.AvgPlayTime > 0
)
SELECT
    fp.GameID,
    fp.Platform,
    fp.SubscriptionStatus,
    AVG(fp.AvgPlayTime) AS AvgPlayTime,
    AVG(fp.PlayTimeRank) AS AvgPlayTimeRank
FROM
    FilteredPlayers fp
GROUP BY ALL
HAVING
    AVG(fp.AvgPlayTime) > 0;
```

SQL features used: `CASE WHEN`, `UNNEST`, `HAVING`, and window functions.

## Exploring the schema [#exploring-the-schema]

Discover all tables and their columns:

```sql
SELECT * FROM information_schema.tables;
SHOW COLUMNS IN <table>;
```

The dataset supports advanced analysis using subresult reuse for query optimization, and it can be
extended with custom data ingested from your own S3 sources.

<InlineCta ctaLabel="Get started for free" ctaUrl="https://go.firebolt.io/signup">
  Spin up an engine and explore this dataset yourself — Firebolt is free to try.
</InlineCta>