WITH match_winner_odds_2025 as (
  SELECT
      home_teams.name  AS home_team,
      away_teams.name  AS away_team,
      matches.date,
      odds.*
  FROM @blt.ultimate_soccer_dataset.matches              AS matches
  JOIN @blt.ultimate_soccer_dataset.teams   AS home_teams ON matches.home_team_id = home_teams.team_id
  JOIN @blt.ultimate_soccer_dataset.teams   AS away_teams ON matches.away_team_id = away_teams.team_id
  JOIN @blt.ultimate_soccer_dataset.seasons AS s          ON matches.season_id   = s.season_id
  JOIN @blt.ultimate_soccer_dataset.competitions AS c     ON s.competition_id    = c.competition_id
  JOIN @blt.ultimate_soccer_dataset.match_betting_odds AS odds
       ON odds.match_id = matches.match_id
  WHERE c.name = 'FIFA Club World Cup'
    AND YEAR(s.start_date) = 2025
    AND odds.market = 'Match Winner'
),
best_odds AS (
    SELECT
        match_id,
        home_team,
        away_team,
        outcome,
        MAX(odds) AS best_odds
    FROM match_winner_odds_2025
    WHERE market = 'Match Winner'
      AND odds_type = 'pre_match'
    GROUP BY match_id, home_team, away_team, outcome
), 
best_probs AS (
    SELECT
        match_id,
        home_team,
        away_team,
        MAX(CASE WHEN outcome = 'Home' THEN 1.0 / best_odds END) AS home_prob,
        MAX(CASE WHEN outcome = 'Draw' THEN 1.0 / best_odds END) AS draw_prob,
        MAX(CASE WHEN outcome = 'Away' THEN 1.0 / best_odds END) AS away_prob
    FROM best_odds
    GROUP BY match_id, home_team, away_team
)
select * from (
SELECT
    home_team || '-' || away_team as match,
    ROUND(home_prob * 100, 1) AS home_pct,
    ROUND(draw_prob * 100, 1) AS draw_pct,
    ROUND(away_prob * 100, 1) AS away_pct,
    ROUND(ROUND(GREATEST(home_prob, draw_prob, away_prob) - LEAST(home_prob, draw_prob, away_prob), 4)*100,0) AS dominance_gap,
    ROUND(GREATEST(home_prob, draw_prob, away_prob)*100) as max_win_probability
FROM best_probs
ORDER BY max_win_probability DESC
LIMIT 5
) ORDER BY 6