1SELECT 
2  m.match_id,
3  m.kickoff_date_utc AS match_date,
4  m.competition_name AS league_competition,
5  m.home_team_name,
6  m.away_team_name,
7  ah_open.bookmaker,
8  ah_open.opening_timestamp AS earliest_ah_timestamp,
9  ah_open.ah_line,
10  ah_open.home_price AS opening_ah_home_price,
11  ah_open.away_price AS opening_ah_away_price,
12  mw_open.mw_timestamp AS earliest_mw_timestamp,
13  mw_open.home_odds AS opening_mw_home_odds,
14  mw_open.draw_odds AS opening_mw_draw_odds,
15  mw_open.away_odds AS opening_mw_away_odds,
16  ah_close.ah_line AS closing_ah_line,
17  ah_close.home_price AS closing_ah_home_price,
18  ah_close.away_price AS closing_ah_away_price,
19  mw_close.home_odds AS closing_mw_home_odds,
20  mw_close.draw_odds AS closing_mw_draw_odds,
21  mw_close.away_odds AS closing_mw_away_odds,
22  m.home_score,
23  m.away_score,
24  CASE 
25    WHEN m.home_score > m.away_score THEN 'Home Win'
26    WHEN m.home_score = m.away_score THEN 'Draw'
27    ELSE 'Away Win'
28  END AS final_result
29FROM "@blt.ultimate_soccer_dataset.matches" m
30INNER JOIN (
31  -- Earliest AH -0.25 opening at 1.80/2.08 or 2.08/1.80
32  SELECT match_id, bookmaker, MIN(collected_at) AS opening_timestamp, outcome, odds
33  FROM "@blt.ultimate_soccer_dataset.match_betting_odds"
34  WHERE market = 'Asian Handicap' AND outcome IN ('Away -0.25', 'Home -0.25')
35  GROUP BY match_id, bookmaker, outcome, odds
36) ah_open ON m.match_id = ah_open.match_id
37-- Continue with Match Winner joins and closing odds joins
38