SELECT
nhl_players.last_name
, nhl_players.first_name
, nhl_players.player_id
, nhl_players.birth_date
, FLOOR(DATEDIFF('2024-10-10', nhl_players.birth_date) / 365) AS age
, nhl_players.rookie
, IF(nhl_players.player_id in (0), 'UFA', nhl_teams.team_code) AS team_code
, nhl_positions.position_abbreviation
, nhl_career_stats_skaters.team_code_finished
, spofo_mag_projections.mag_top400_rk AS db_rank
, ed_sportscodes.tsf_global AS tsf_global
, (spofo_mag_projections.g + spofo_mag_projections.a) AS predicted_points
, spofo_mag_projections.g AS predicted_goals
, spofo_mag_projections.a AS predicted_assists
, spofo_mag_projections.gp AS predicted_GP
, IF(nhl_career_stats_skaters.gp IS NULL, '0', nhl_career_stats_skaters.gp) AS actual_GP
, IF(nhl_career_stats_skaters.gp IS NULL, '—', nhl_career_stats_skaters.g) AS actual_goals
, IF(nhl_career_stats_skaters.gp IS NULL, '—', nhl_career_stats_skaters.a) AS actual_assists
, IF(nhl_career_stats_skaters.gp IS NULL, '—', nhl_career_stats_skaters.pts) AS actual_points
, IF(nhl_career_stats_skaters.gp IS NULL, '—', nhl_career_stats_skaters.plus_minus) AS actual_plus_minus
, IF(nhl_career_stats_skaters.gp IS NULL, '—', nhl_career_stats_skaters.pts / nhl_career_stats_skaters.gp) AS actual_pts_per_gp
, IF(nhl_career_stats_skaters.gp IS NULL, '—', nhl_career_stats_skaters.pim) AS actual_pim
, spofo_mag_projections.injury_concern
, spofo_mag_projections.must_draft
, spofo_mag_projections.contract_year
, spofo_mag_projections.fantasy_tags_gte
FROM
spofo_mag_projections
LEFT JOIN nhl_players on nhl_players.player_id = spofo_mag_projections.player_id
LEFT OUTER JOIN nhl_career_stats_skaters ON
nhl_career_stats_skaters.player_id = spofo_mag_projections.player_id AND (nhl_career_stats_skaters.date_desc = '2023-24' AND nhl_career_stats_skaters.league = 'NHL' AND
nhl_career_stats_skaters.date_sort = '2023.9' AND nhl_career_stats_skaters.line_desc = 'normal')
LEFT JOIN ed_sportscodes ON
spofo_mag_projections.player_id = ed_sportscodes.tsf
AND ed_sportscodes.league_id = 'hockey'
LEFT JOIN nhl_positions ON
nhl_players.position_1 = nhl_positions.position_id
LEFT JOIN nhl_teams ON
nhl_players.team_id = nhl_teams.team_id
WHERE
TRUE
AND spofo_mag_projections.season = '2024-25'
AND spofo_mag_projections.gp IS NOT NULL
AND spofo_mag_projections.mag_top400_rk < 401
AND nhl_positions.position_abbreviation != 'G'
ORDER BY
spofo_mag_projections.mag_top400_rk ASC
LIMIT , 50
;
-topskaters