1WITH match_winner_odds_2025 as (
2  SELECT
3      home_teams.name  AS home_team,
4      away_teams.name  AS away_team,
5      matches.date,
6      odds.*
7  FROM @blt.ultimate_soccer_dataset.matches              AS matches
8  JOIN @blt.ultimate_soccer_dataset.teams   AS home_teams ON matches.home_team_id = home_teams.team_id
9  JOIN @blt.ultimate_soccer_dataset.teams   AS away_teams ON matches.away_team_id = away_teams.team_id
10  JOIN @blt.ultimate_soccer_dataset.seasons AS s          ON matches.season_id   = s.season_id
11  JOIN @blt.ultimate_soccer_dataset.competitions AS c     ON s.competition_id    = c.competition_id
12  JOIN @blt.ultimate_soccer_dataset.match_betting_odds AS odds
13       ON odds.match_id = matches.match_id
14  WHERE c.name = 'FIFA Club World Cup'
15    AND YEAR(s.start_date) = 2025
16    AND odds.market = 'Match Winner'
17),
18best_odds AS (
19    SELECT
20        match_id,
21        home_team,
22        away_team,
23        outcome,
24        MAX(odds) AS best_odds
25    FROM match_winner_odds_2025
26    WHERE market = 'Match Winner'
27      AND odds_type = 'pre_match'
28    GROUP BY match_id, home_team, away_team, outcome
29), 
30best_probs AS (
31    SELECT
32        match_id,
33        home_team,
34        away_team,
35        MAX(CASE WHEN outcome = 'Home' THEN 1.0 / best_odds END) AS home_prob,
36        MAX(CASE WHEN outcome = 'Draw' THEN 1.0 / best_odds END) AS draw_prob,
37        MAX(CASE WHEN outcome = 'Away' THEN 1.0 / best_odds END) AS away_prob
38    FROM best_odds
39    GROUP BY match_id, home_team, away_team
40)
41select * from (
42SELECT
43    home_team || '-' || away_team as match,
44    ROUND(home_prob * 100, 1) AS home_pct,
45    ROUND(draw_prob * 100, 1) AS draw_pct,
46    ROUND(away_prob * 100, 1) AS away_pct,
47    ROUND(ROUND(GREATEST(home_prob, draw_prob, away_prob) - LEAST(home_prob, draw_prob, away_prob), 4)*100,0) AS dominance_gap,
48    ROUND(GREATEST(home_prob, draw_prob, away_prob)*100) as max_win_probability
49FROM best_probs
50ORDER BY max_win_probability DESC
51LIMIT 5
52) ORDER BY 6