Player Transfers and Career Tracking

Player career data spans multiple teams, multiple seasons, and potentially multiple leagues. Tracking how a player develops over their career — and how their performance changes after a transfer — requires SQL that handles slowly changing dimension (SCD) patterns: the fact that a player's team_id can change over time.

This lesson covers career aggregation, transfer impact analysis, and identifying peak performance seasons.

The Core Challenge: Players Change Teams

In a typical sports database, a player's team_id reflects their current team. Historical data about which team they played for in each season is usually embedded in the player_stats table through the matches they played.

The trick is to derive the team a player was at in each season by joining through matches:

-- Determine which team a player represented in each season
SELECT DISTINCT
    ps.player_id,
    m.season,
    CASE WHEN m.home_team_id = p.team_id THEN m.home_team_id
         ELSE m.away_team_id END AS team_that_season
FROM player_stats ps
JOIN matches m ON m.match_id = ps.match_id
JOIN players p ON p.player_id = ps.player_id
ORDER BY ps.player_id, m.season;

This works because each match has home_team_id and away_team_id — we can infer which side a player was on by seeing which team they were registered with at match time. In practice, data providers often include a team_id directly in the player_stats table; the above is a workaround when it's absent.