SportStats FIFA Championship Analytics - Activity 05 🏆

docker-compose up -d
docker exec -it w4-postgres psql -U postgres fifa_db

-- Teams: National teams competing
teams (team_id, name, country, group_letter, fifa_ranking)

-- Players: Registered players
players (player_id, name, team_id, position, jersey_number, age)
  └── position: 'Forward', 'Midfielder', 'Goalkeeper'

-- Matches: Tournament games
matches (match_id, home_team_id, away_team_id, match_date, stage, home_score, away_score, stadium)
  └── stage: 'Group', 'Round of 16', 'Quarter', 'Semi', 'Final'

-- Goals: Individual scoring records
goals (goal_id, match_id, player_id, team_id, minute, goal_type)
  └── goal_type: 'Regular', 'Penalty', 'Own Goal', 'Free Kick'

-- Golden Boot Race (Top Scorers)
SELECT p.name, t.name as team, COUNT(g.goal_id) as goals
FROM goals g
JOIN players p ON g.player_id = p.player_id
JOIN teams t ON p.team_id = t.team_id
GROUP BY p.player_id
ORDER BY goals DESC
LIMIT 10;

-- Team Standings with Goal Difference
SELECT
    t.name,
    SUM(CASE WHEN m.home_team_id = t.team_id THEN m.home_score
             WHEN m.away_team_id = t.team_id THEN m.away_score END) as goals_for,
    SUM(CASE WHEN m.home_team_id = t.team_id THEN m.away_score
             WHEN m.away_team_id = t.team_id THEN m.home_score END) as goals_against
FROM teams t
JOIN matches m ON t.team_id IN (m.home_team_id, m.away_team_id)
GROUP BY t.team_id
ORDER BY goals_for DESC;

-- Goals by Type (Analytics)
SELECT goal_type, COUNT(*) as count,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM goals), 1) as percentage
FROM goals
GROUP BY goal_type
ORDER BY count DESC;

-- Window Function: Player Ranking
SELECT
    p.name,
    t.name as team,
    COUNT(g.goal_id) as goals,
    ROW_NUMBER() OVER (ORDER BY COUNT(g.goal_id) DESC) as rank
FROM players p
JOIN teams t ON p.team_id = t.team_id
LEFT JOIN goals g ON p.player_id = g.player_id
GROUP BY p.player_id
ORDER BY goals DESC;