1with fifa_club_world_cup_matches as (
2select home_teams.name as home_team, away_teams.name as away_team, matches.*, odds.*
3from @blt.ultimate_soccer_dataset.matches matches
4inner join @blt.ultimate_soccer_dataset.teams home_teams ON (matches.home_team_id = home_teams.team_id)
5inner join @blt.ultimate_soccer_dataset.teams away_teams ON (matches.away_team_id = away_teams.team_id)
6inner join @blt.ultimate_soccer_dataset.seasons s ON (matches.season_id = s.season_id)
7inner join @blt.ultimate_soccer_dataset.competitions c ON (s.competition_id = c.competition_id)
8inner join @blt.ultimate_soccer_dataset.match_betting_odds odds ON (odds.match_id = matches.match_id)
9where 1=1
10      and c.name = 'FIFA Club World Cup'
11      and year(s.start_date) = 2025      
12      and market = 'Match Winner'
13      and matches.status = 'Match Finished'
14),
15pre_match AS (  -- 1. keep only pre-match “Match Winner” odds
16    SELECT
17        match_id,
18        outcome,                           -- 'Home', 'Draw', 'Away'
19        AVG(odds) AS avg_odds              -- average across bookmakers
20    FROM fifa_club_world_cup_matches
21    WHERE market     = 'Match Winner'
22      AND odds_type  = 'pre_match'
23    GROUP BY match_id, outcome
24),
25pivot1 AS (         -- 2. put the three outcomes on one row per match
26    SELECT
27        match_id,
28        MIN(CASE WHEN outcome = 'Home' THEN avg_odds END) AS home_odds,
29        MIN(CASE WHEN outcome = 'Draw' THEN avg_odds END) AS draw_odds,
30        MIN(CASE WHEN outcome = 'Away' THEN avg_odds END) AS away_odds
31    FROM pre_match
32    GROUP BY match_id
33),
34prob AS (          -- 3. convert odds → implied probabilities (raw)
35    SELECT
36        p.match_id,
37        1 / p.home_odds  AS raw_home_prob,
38        1 / p.draw_odds  AS raw_draw_prob,
39        1 / p.away_odds  AS raw_away_prob,
40        m.home_team,
41        m.away_team,
42        m.date,
43        m.home_score,
44        m.away_score
45    FROM pivot1 p
46    JOIN fifa_club_world_cup_matches m USING (match_id)
47    GROUP BY
48        p.match_id, p.home_odds, p.draw_odds, p.away_odds,
49        m.home_team, m.away_team, m.date, m.home_score, m.away_score
50),
51normalised AS (    -- 4. normalise so the three probs sum to 1
52    SELECT
53        *,
54        raw_home_prob + raw_draw_prob + raw_away_prob AS total_p
55    FROM prob
56),
57final AS (         -- 5. map actual result → its pre-match probability
58    SELECT
59        *,
60        raw_home_prob / total_p AS home_prob,
61        raw_draw_prob / total_p AS draw_prob,
62        raw_away_prob / total_p AS away_prob,
63        CASE
64            WHEN home_score > away_score THEN 'home'
65            WHEN home_score < away_score THEN 'away'
66            ELSE 'draw'
67        END AS actual_outcome
68    FROM normalised
69)
70SELECT
71    home_team || ' - ' || away_team as teams,
72    --home_team,
73    --away_team,
74    strftime(date, '%Y-%m-%d') as match_date,
75    home_score,
76    away_score,
77    --round(home_prob,2) as home_probability,
78    --round(draw_prob,2) as draw_probability,
79    --round(away_prob,2) as away_probability,
80    CASE
81            WHEN home_prob >= draw_prob AND home_prob >= away_prob THEN 'home'
82            WHEN away_prob >= draw_prob AND away_prob >  home_prob THEN 'away'
83            ELSE 'draw'                                        -- ties favour draw
84    END AS favourite,
85    round(greatest(home_prob, draw_prob, away_prob), 2) as favourite_probability, 
86    actual_outcome,
87    round(CASE
88        WHEN actual_outcome = 'home' THEN home_prob
89        WHEN actual_outcome = 'away' THEN away_prob
90        ELSE draw_prob
91    END, 2) AS actual_probability,                -- probability bookmakers gave to what happened
92    CASE WHEN favourite_probability = actual_probability THEN 'Yes' ELSE 'No' END as correct_odds,
93FROM final
94ORDER BY actual_probability ASC