F1 Data Warehouse — OLAP Demo

A self-contained PostgreSQL + pgAdmin environment for demonstrating OLAP queries. The database is pre-loaded with Formula 1 race data.

Requirements

Docker with Docker Compose

Quick Start

docker compose up -d

Then open http://localhost:5050 in a browser.

pgAdmin opens directly — no login required. Expand Servers → F1 Data Warehouse in the left panel, right-click → Query Tool, and enter the database password when prompted:

Field	Value
Username	`f1user`
Password	`f1pass`

Database Schema

13 tables covering F1 seasons from 2010 onwards:

Table	Description
`circuits`	Race circuits with location and coordinates
`seasons`	Championship years
`races`	Individual race events per season
`drivers`	Driver profiles
`constructors`	Constructor (team) profiles
`results`	Race finishing results per driver
`driverstandings`	Driver championship standings after each race
`constructorstandings`	Constructor championship standings after each race
`constructorresults`	Constructor points per race
`qualifying`	Qualifying session times (Q1/Q2/Q3)
`pitstops`	Pit stop times per driver per race
`sprintresults`	Sprint race results
`status`	Lookup table for race finish statuses

Schema Diagrams

Dimensional Fact Model

Star schema centred on results. Blue nodes are core dimensions, green is the bridge dimension (races), purple is the lookup dimension (status), and orange is the fact table.

flowchart LR
    SEASONS["seasons\n─────────\nyear"]
    CIRCUITS["circuits\n─────────\nname\nlocation\ncountry"]
    RACES["races\n─────────\nname\ndate\nround"]
    DRIVERS["drivers\n─────────\nforename\nsurname\nnationality\ndob"]
    CONSTRUCTORS["constructors\n─────────\nname\nnationality"]
    STATUS["status\n─────────\nstatus"]
    RESULTS["RESULTS ★\n══════════\npoints\ngrid\npositionOrder\nlaps\nmilliseconds\nfastestLap\nfastestLapSpeed"]

    SEASONS -->|year| RACES
    CIRCUITS -->|circuitId| RACES
    RACES -->|raceId| RESULTS
    DRIVERS -->|driverId| RESULTS
    CONSTRUCTORS -->|constructorId| RESULTS
    STATUS -->|statusId| RESULTS

    style RESULTS fill:#E8923A,stroke:#B35C00,color:#fff
    style RACES fill:#5A9E6F,stroke:#2E7D32,color:#fff
    style SEASONS fill:#4A90D9,stroke:#1565C0,color:#fff
    style CIRCUITS fill:#4A90D9,stroke:#1565C0,color:#fff
    style DRIVERS fill:#4A90D9,stroke:#1565C0,color:#fff
    style CONSTRUCTORS fill:#4A90D9,stroke:#1565C0,color:#fff
    style STATUS fill:#9C6BB5,stroke:#6A1B9A,color:#fff

The remaining six tables are secondary fact tables that share the same dimensions:

Secondary Fact	Dimensions used
`pitstops`	races, drivers
`qualifying`	races, drivers, constructors
`sprintresults`	races, drivers, constructors, status
`constructorresults`	races, constructors
`driverstandings`	races, drivers
`constructorstandings`	races, constructors

Entity-Relationship Diagram

erDiagram
    seasons {
        int year PK
    }
    circuits {
        int circuitId PK
        varchar circuitRef
        varchar name
        varchar location
        varchar country
        float lat
        float lng
        int alt
    }
    races {
        int raceId PK
        int year FK
        int round
        int circuitId FK
        varchar name
        date date
    }
    status {
        int statusId PK
        varchar status
    }
    drivers {
        int driverId PK
        varchar driverRef
        int number
        varchar code
        varchar forename
        varchar surname
        date dob
        varchar nationality
    }
    constructors {
        int constructorId PK
        varchar constructorRef
        varchar name
        varchar nationality
    }
    results {
        int resultId PK
        int raceId FK
        int driverId FK
        int constructorId FK
        int statusId FK
        int number
        int grid
        int position
        varchar positionText
        int positionOrder
        float points
        int laps
        varchar time
        int milliseconds
        int fastestLap
        int rank
        varchar fastestLapTime
        varchar fastestLapSpeed
    }
    pitstops {
        int raceId PK
        int driverId PK
        int stop PK
        int lap
        time time
        varchar duration
        int milliseconds
    }
    qualifying {
        int qualifyId PK
        int raceId FK
        int driverId FK
        int constructorId FK
        int number
        int position
        varchar q1
        varchar q2
        varchar q3
    }
    constructorresults {
        int constructorResultsId PK
        int raceId FK
        int constructorId FK
        float points
        varchar status
    }
    constructorstandings {
        int constructorStandingsId PK
        int raceId FK
        int constructorId FK
        float points
        int position
        varchar positionText
        int wins
    }
    driverstandings {
        int driverStandingsId PK
        int raceId FK
        int driverId FK
        float points
        int position
        varchar positionText
        int wins
    }
    sprintresults {
        int sprintResultId PK
        int raceId FK
        int driverId FK
        int constructorId FK
        int statusId FK
        int number
        int grid
        int position
        varchar positionText
        int positionOrder
        float points
        int laps
        varchar time
        int milliseconds
        int fastestLap
        varchar fastestLapTime
    }

    seasons ||--o{ races : "year"
    circuits ||--o{ races : "circuitId"
    races ||--o{ results : "raceId"
    drivers ||--o{ results : "driverId"
    constructors ||--o{ results : "constructorId"
    status ||--o{ results : "statusId"
    races ||--o{ pitstops : "raceId"
    drivers ||--o{ pitstops : "driverId"
    races ||--o{ qualifying : "raceId"
    drivers ||--o{ qualifying : "driverId"
    constructors ||--o{ qualifying : "constructorId"
    races ||--o{ constructorresults : "raceId"
    constructors ||--o{ constructorresults : "constructorId"
    races ||--o{ constructorstandings : "raceId"
    constructors ||--o{ constructorstandings : "constructorId"
    races ||--o{ driverstandings : "raceId"
    drivers ||--o{ driverstandings : "driverId"
    races ||--o{ sprintresults : "raceId"
    drivers ||--o{ sprintresults : "driverId"
    constructors ||--o{ sprintresults : "constructorId"
    status ||--o{ sprintresults : "statusId"

Connection Details


Host	`localhost`
Port	`5432`
Database	`f1_dw`
Username	`f1user`
Password	`f1pass`

These credentials work for any external SQL client (DBeaver, DataGrip, psql, etc.).

Lifecycle Commands

# Start
docker compose up -d

# Stop (data is preserved)
docker compose down

# Full reset — wipes all data and re-initialises from SQL files
docker compose down -v && docker compose up -d

Sample OLAP Queries

1. Ferrari Pit Stop Durations with Running Average (Window Function)

Per-stop duration and year-to-date running average for Ferrari drivers in 2023, excluding outliers ≥ 60 s.

SELECT 
    ra.name AS race_name,
    ra.round AS gp_round, 
    d.surname AS driver,          
    (p.milliseconds / 1000.0) AS stop_duration_secs,
    AVG(p.milliseconds / 1000.0) OVER (
        PARTITION BY p.driverId
        ORDER BY ra.round, p.stop
    ) AS ferrari_avg_duration_ytd
FROM 
    pitstops p
    JOIN races ra ON p.raceId = ra.raceId
    JOIN results re ON p.raceId = re.raceId AND p.driverId = re.driverId
    JOIN constructors c ON re.constructorId = c.constructorId
    JOIN drivers d ON p.driverId = d.driverId
WHERE 
    c.name = 'Ferrari'
    AND ra.year = 2023
    AND (p.milliseconds / 1000.0) < 60.0
ORDER BY 
    ra.round, 
    p.stop;

2. Podium Points for First-Time Podium Finishers in 2023 (ROLLUP + NOT EXISTS)

Constructor and driver podium points for the 2023 season, restricted to drivers who reached the top 3 in 2023 but never did so in 2022. ROLLUP adds a subtotal row per constructor.

SELECT 
    c.name AS team_name,
    d.surname AS driver_name,
    SUM(re.points) AS podium_points
FROM 
    results re
    JOIN races ra ON re.raceId = ra.raceId
    JOIN constructors c ON re.constructorId = c.constructorId
    JOIN drivers d ON re.driverId = d.driverId
WHERE 
    ra.year = 2023
    AND re.positionOrder <= 3
    AND NOT EXISTS (
        SELECT 1
        FROM results re_old
        JOIN races ra_old ON re_old.raceId = ra_old.raceId
        WHERE 
            re_old.driverId = d.driverId  
            AND ra_old.year = 2022
            AND re_old.positionOrder <= 3
    )
GROUP BY 
    ROLLUP(c.name, d.surname)
ORDER BY 
    c.name ASC, 
    GROUPING(d.surname) ASC,
    SUM(re.points) DESC;

3. Hamilton 2023 — Places Gained per Race with LAG and Running Average (LAG + AVG)

Start vs. finish position for each race, the change vs. the previous race, and the year-to-date running average of places gained.

SELECT 
    ra.round AS grand_prix_round,
    ra.name AS race_name,
    re.grid AS start_position,
    re.positionOrder AS end_position,
    (re.grid - re.positionOrder) AS gained_places,
    (re.grid - re.positionOrder) - LAG(re.grid - re.positionOrder, 1) OVER w AS gain_diff_to_last_race,
    ROUND(AVG(re.grid - re.positionOrder) OVER w, 2) AS avg_gained_ytd
FROM 
    results re
    JOIN races ra ON re.raceId = ra.raceId
    JOIN drivers d ON re.driverId = d.driverId
WHERE 
    d.surname = 'Hamilton'  
    AND ra.year = 2023
WINDOW w AS (
    ORDER BY ra.round
)
ORDER BY 
    ra.round;

Project Structure

.
├── ddl.sql              # Table definitions
├── data.sql             # Seed data
├── docker-compose.yml
└── pgadmin/
    └── servers.json     # Pre-configured pgAdmin connection

Navigation Menu

Repository files navigation

F1 Data Warehouse — OLAP Demo

Requirements

Quick Start

Database Schema

Schema Diagrams

Dimensional Fact Model

Entity-Relationship Diagram

Connection Details

Lifecycle Commands

Sample OLAP Queries

1. Ferrari Pit Stop Durations with Running Average (Window Function)

2. Podium Points for First-Time Podium Finishers in 2023 (ROLLUP + NOT EXISTS)

3. Hamilton 2023 — Places Gained per Race with LAG and Running Average (LAG + AVG)

Project Structure

About

Releases

Packages

Contributors

Name		Name	Last commit message	Last commit date
pgadmin		pgadmin
README.md		README.md
data.sql		data.sql
ddl.sql		ddl.sql
docker-compose.yml		docker-compose.yml

Latest commit

History

Folders and files

Repository files navigation

F1 Data Warehouse — OLAP Demo

Requirements

Quick Start

Database Schema

Schema Diagrams

Dimensional Fact Model

Entity-Relationship Diagram

Connection Details

Lifecycle Commands

Sample OLAP Queries

1. Ferrari Pit Stop Durations with Running Average (Window Function)

2. Podium Points for First-Time Podium Finishers in 2023 (ROLLUP + NOT EXISTS)

3. Hamilton 2023 — Places Gained per Race with LAG and Running Average (LAG + AVG)

Project Structure

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors