1WITH
2  match_odds AS (
3    SELECT
4      m.match_id,
5      m.date,
6      m.home_team_name,
7      m.away_team_name,
8      m.home_score,
9      m.away_score,
10      CASE
11        WHEN m.home_score > m.away_score THEN 'Home'
12        WHEN m.away_score > m.home_score THEN 'Away'
13        ELSE 'Draw'
14      END AS actual_result,
15      bo.outcome,
16      bo.odds
17    FROM
18      "@blt.ultimate_soccer_dataset.matches" m
19      JOIN "@blt.ultimate_soccer_dataset.match_betting_odds" bo ON m.match_id = bo.match_id
20    WHERE
21      m.status = 'Match Finished'
22      AND bo.odds_type = 'pre_match'
23      AND bo.market = 'Match Winner'
24      AND m.date >= '2025-01-01'
25  )
26SELECT
27  outcome,
28  COUNT(*) AS total_bets,
29  SUM(
30    CASE
31      WHEN outcome = actual_result THEN 1
32      ELSE 0
33    END
34  ) AS correct_predictions,
35  ROUND(
36    100.0 * SUM(
37      CASE
38        WHEN outcome = actual_result THEN 1
39        ELSE 0
40      END
41    ) / COUNT(*),
42    1
43  ) AS accuracy_pct,
44  ROUND(AVG(odds), 2) AS avg_odds,
45  ROUND(1.0 / AVG(odds), 4) AS avg_implied_prob,
46  ROUND(MIN(odds), 2) AS min_odds,
47  ROUND(MAX(odds), 2) AS max_odds,
48  ROUND(STDDEV(odds), 3) AS odds_stddev
49FROM
50  match_odds
51GROUP BY
52  outcome
53ORDER BY
54  accuracy_pct DESC