Calgary Flames shot analysis

No ties, no unknowns. severity: error

select count(*) as failures from (
with all_values as (

    select
        result as value_field,
        count(*) as n_records

    from main.flames_games
    group by result

)

select *
from all_values
where value_field not in (
    'W','L','OTL'
)
) dbt_test

At least 95% of unblocked attempts should land in the attacking half. If the API changes its side convention, this catches it. severity: warn

select count(*) as failures from (
select avg((x > 0)::integer) as share_in_attacking_half
from main.flames_shots
where event <> 'blocked-shot'
having avg((x > 0)::integer) < 0.95
) dbt_test

Standings logic is applied consistently. severity: error

select count(*) as failures from (
select * from main.flames_games where not (points = case result when 'W' then 2 when 'OTL' then 1 else 0 end)
) dbt_test

A rink is 200 by 85 feet. severity: error

select count(*) as failures from (
select * from main.flames_shots where not (x between -100 and 100 and y between -43 and 43)
) dbt_test

Counts goal events per game and compares them to the official score, allowing for the extra goal a shootout winner is credited. severity: error

select count(*) as failures from (
-- Returns games whose shot-level goals don't match the official score.
with shot_goals as (
    select game_id,
           count(*) filter (where is_goal and team = 'CGY') as gf,
           count(*) filter (where is_goal and team = 'OPP') as ga
    from main.flames_shots
    group by 1
)

select g.game_id
from main.flames_games g
left join shot_goals s using (game_id)
where g.goals_for - (g.decided_in = 'SO' and g.result = 'W')::integer <> coalesce(s.gf, 0)
   or g.goals_against - (g.decided_in = 'SO' and g.result <> 'W')::integer <> coalesce(s.ga, 0)
) dbt_test

A few events arrive without a matching roster entry. severity: warn

select count(*) as failures from (
select shooter
from main.flames_shots
where shooter is null
) dbt_test

Referential integrity between the two served tables. severity: error

select count(*) as failures from (
with child as (
    select game_id as from_field
    from main.flames_shots
    where game_id is not null
),

parent as (
    select game_id as to_field
    from main.flames_games
)

select
    from_field

from child
left join parent
    on child.from_field = parent.to_field

where parent.to_field is null
) dbt_test

Guards against a partial extract replacing full seasons. severity: error

select count(*) as failures from (
select count(*) as row_count from main.flames_games having count(*) < 82
) dbt_test

One row per game. severity: error

select count(*) as failures from (
select
    game_id as unique_field,
    count(*) as n_records

from main.flames_games
where game_id is not null
group by game_id
having count(*) > 1
) dbt_test