update last play logic

[gavh3]

Overview

• Simplify the logic on last plays, improves performance a bit

Checks

• [ ] All models ran successfully
• [ ] All tests passed
• [ ] Changes to models are reflected in the schema.yml

[gavh3]

@dmf95 ran this with no row discrepancies

with compare_a as (
SELECT
  game_id
  , event_idx as current_event_idx
  , player_index
  , play_time
  , player_role_team
  , event_type
  , event_secondary_type
  , event_description
  , last_play_event_idx
  , last_player_role_team
  , last_play_event_type
  , last_play_event_secondary_type
  , last_play_event_description
  , goals_home
  , goals_away
  , goals_home_lag
  , goals_away_lag
FROM `nhl-breakouts.dbt_gavin.stg_nhl__live_plays`
-- where game_id = 2022020622
-- order by event_idx, player_index, play_time asc
except distinct
SELECT
  game_id
  , event_idx as current_event_idx
  , player_index
  , play_time
  , player_role_team
  , event_type
  , event_secondary_type
  , event_description
  , last_play_event_idx
  , last_player_role_team
  , last_play_event_type
  , last_play_event_secondary_type
  , last_play_event_description
  , goals_home
  , goals_away
  , goals_home_lag
  , goals_away_lag
FROM `nhl-breakouts.dbt_dom.stg_nhl__live_plays`
-- where game_id = 2022020622
-- order by event_idx, player_index, play_time asc
)

, compare_b as (
SELECT
  game_id
  , event_idx as current_event_idx
  , player_index
  , play_time
  , player_role_team
  , event_type
  , event_secondary_type
  , event_description
  , last_play_event_idx
  , last_player_role_team
  , last_play_event_type
  , last_play_event_secondary_type
  , last_play_event_description
  , goals_home
  , goals_away
  , goals_home_lag
  , goals_away_lag
FROM `nhl-breakouts.dbt_dom.stg_nhl__live_plays`
-- where game_id = 2022020622
-- order by event_idx, player_index, play_time asc
except distinct
SELECT
  game_id
  , event_idx as current_event_idx
  , player_index
  , play_time
  , player_role_team
  , event_type
  , event_secondary_type
  , event_description
  , last_play_event_idx
  , last_player_role_team
  , last_play_event_type
  , last_play_event_secondary_type
  , last_play_event_description
  , goals_home
  , goals_away
  , goals_home_lag
  , goals_away_lag
FROM `nhl-breakouts.dbt_gavin.stg_nhl__live_plays`
-- where game_id = 2022020622
-- order by event_idx, player_index, play_time asc
)

select * from compare_a
union all
select * from compare_b