Starter code for hands-on exercises

dbt_nz_lab_starter_code.sql

-- Exercise 1: Define a staging model (stg_player.sql)

with player as (
    select * from {{ source('fifa', 'player') }}
)

select 
    id as player_id
    , affiliation_id
    , concat(player_first_name, ' ', player_last_name) as player_name
    , weight
    , height
    , city
    , birth_date
    -- obtain age at 2018 world cup start
    , datediff(year,birth_date,'2018-06-14') as age
from player


-- Exercise 2: Enrich our model (dim_players.sql) 

with stg_player as (
    select * from {{ ref('stg_player') }}
)
, stg_team as (
    select * from {{ ref('stg_team') }}
)
, final as (
    select 
        stg_player.player_id
        , stg_player.affiliation_id
        , stg_player.player_name
        , stg_player.weight
        , stg_player.height
        , stg_player.city
        , stg_player.birth_date
        , stg_player.age
        , stg_team.team_name
        , stg_team.country_code
    from stg_player
    left join stg_team on stg_player.affiliation_id = stg_team.affiliation_id
)

select * from final

-- Exercise 3: Test our event stream data 

    columns:
      - name: player_id
        tests:
        - not_null



-- Exercise 4: analyze player characteristics  (player_facts.sql)


with dim_players as (
    select * from {{ ref('dim_players') }}
)
, fct_events as (
    select * from {{ ref('fct_events') }}
) 
,final as (

    select 
        dim_players.player_id
        , player_name
        , weight
        , height
        , city
        , birth_date
        , affiliation_id
        , team_name
        , country_code
        -- example for one event type of interest
        , sum (case when event_type_name = 'goal' then 1 else 0 end) as goal_count
    
    from dim_players
    left join fct_events on dim_players.player_id = fct_events.player_id
    group by 1,2,3,4,5,6,7,8,9
)

select * from final