SELECT
    sf.*,  
    floor(datediff('2024-10-10', p.birth_date) / 365) AS age, 
    p.player_id, 
    p.shoots,  
    p.team_id, 
    t.team_name, 
    t.city_team_name, 
    t.city,  
    t.team_code, 
    p.drafted_year,
    CASE 
    WHEN p.last_amateur_team_before_draft IS NOT NULL THEN p.last_amateur_team_before_draft
    WHEN sf.last_name = 'McKenna' THEN '2026 draft eligible'
    WHEN sf.last_name = 'Hagens' THEN '2025 draft eligible'
    END AS last_amateur_team_before_draft
FROM spofo_mag_fantasy_top_100 sf
    LEFT JOIN nhl_players p ON sf.player_id = p.player_id
    LEFT JOIN nhl_teams t ON p.team_id = t.team_id
WHERE sf.season = '2024-25'
ORDER BY sf.rank 
LIMIT 100
;