1-- Query 2 - FINAL: Handle duplicate team records in Baselight
2WITH team_season_performance AS (
3  SELECT 
4    ms.team_id,
5    -- Use DISTINCT to get unique team info (handles duplicate team records)
6    MAX(t.name) as team_name,
7    MAX(c.name) as league,
8    MAX(c.country) as country,
9    -- Get xG directly from match_stats (no team table join duplication)
10    SUM(CAST(ms.value AS DOUBLE)) as total_xg,
11    -- Calculate goals and points
12    SUM(CASE 
13      WHEN m.home_team_id = ms.team_id THEN COALESCE(m.home_score, 0)
14      WHEN m.away_team_id = ms.team_id THEN COALESCE(m.away_score, 0)
15      ELSE 0
16    END) as total_goals,
17    SUM(CASE 
18      WHEN m.home_team_id = ms.team_id AND m.home_score > m.away_score THEN 3
19      WHEN m.away_team_id = ms.team_id AND m.away_score > m.home_score THEN 3
20      WHEN m.home_score = m.away_score THEN 1
21      ELSE 0
22    END) as total_points,
23    SUM(CASE 
24      WHEN m.home_team_id = ms.team_id THEN COALESCE(m.home_score, 0) - COALESCE(m.away_score, 0)
25      WHEN m.away_team_id = ms.team_id THEN COALESCE(m.away_score, 0) - COALESCE(m.home_score, 0)
26      ELSE 0
27    END) as goal_difference,
28    COUNT(DISTINCT ms.match_id) as games_played
29  FROM "@blt.ultimate_soccer_dataset.match_stats" ms
30  JOIN "@blt.ultimate_soccer_dataset.matches" m ON ms.match_id = m.match_id
31  JOIN "@blt.ultimate_soccer_dataset.seasons" s ON m.season_id = s.season_id
32  JOIN "@blt.ultimate_soccer_dataset.competitions" c ON s.competition_id = c.competition_id
33  -- Use DISTINCT to avoid duplicate team issues
34  JOIN (SELECT DISTINCT team_id, name, country FROM "@blt.ultimate_soccer_dataset.teams") t ON ms.team_id = t.team_id
35  WHERE s.season_id IN (
36    '8a5c7169-9664-5691-9297-6d41a5d72ec9', -- La Liga 2024/25
37    '49d5dcd6-7a63-5354-8774-c6cdfa41d771', -- Ligue 1 2024/25
38    'c9251047-c6a9-56bf-9861-60bcb289bf10', -- Premier League 2024/25
39    '7b34e935-9086-5c7d-852b-a82ba18c31be', -- Primeira Liga 2024/25
40  )
41    AND c.name IN ('Premier League', 'La Liga', 'Serie A', 'Bundesliga', 'Ligue 1', 'Primeira Liga')
42    AND m.status = 'Match Finished'
43    AND ms.type = 'expected_goals'
44  GROUP BY ms.team_id
45)
46SELECT 
47  team_name,
48  league,
49  country,
50  ROUND(total_xg, 2) as total_xg,
51  total_points,
52  goal_difference,
53  total_goals,
54  games_played,
55  RANK() OVER (PARTITION BY league ORDER BY total_points DESC, goal_difference DESC) as current_position
56FROM team_season_performance
57WHERE games_played >= 2
58ORDER BY total_xg DESC;