Building Intermediate/Marts Models

{{ config(materialized='table') }}

WITH races AS (
    SELECT * FROM {{ ref('stg_f1_races') }}
),

circuits AS (
    SELECT * FROM {{ ref('stg_f1_circuits') }}
),

results AS (
    SELECT * FROM {{ ref('stg_f1_results') }}
),

constructors AS (
    SELECT * FROM {{ ref('stg_f1_constructors') }}
),

driver_standings AS (
    SELECT * FROM {{ ref('stg_f1_driver_standings') }}
),

-- Join race data with circuit information
race_details AS (
    SELECT
        r.race_id,
        r.year,
        r.round,
        r.name AS race_name,
        c.circuit_name,
        c.location,
        c.country,
        r.date AS race_date
    FROM races r
    LEFT JOIN circuits c ON r.circuit_id = c.circuit_id
),

-- Join results with constructors
race_results AS (
    SELECT
        res.race_id,
        res.driver_id,
        res.constructor_id,
        con.constructor_name,
        con.nationality AS constructor_nationality,
        res.grid,
        res.position,
        res.points
    FROM results res
    LEFT JOIN constructors con ON res.constructor_id = con.constructor_id
)

-- Final combined query
SELECT
    rd.race_id,
    rd.year,
    rd.round,
    rd.race_name,
    rd.circuit_name,
    rd.country,
    rd.race_date,
    rr.constructor_id,
    rr.constructor_name,
    rr.constructor_nationality,
    rr.driver_id,
    rr.grid,
    rr.position,
    rr.points,
    ds.position AS driver_standing_position,
    ds.points AS driver_total_points
FROM race_details rd
JOIN race_results rr ON rd.race_id = rr.race_id
LEFT JOIN driver_standings ds ON rr.driver_id = ds.driver_id AND rd.race_id = ds.race_id
ORDER BY rd.year, rd.round, rr.position