1WITH ranked AS (
2  SELECT
3    match_id, market, outcome, odds, collected_at,
4    ROW_NUMBER() OVER (
5      PARTITION BY match_id, market, outcome
6      ORDER BY collected_at ASC          -- erste = Opening
7    ) AS rn
8  FROM "@blt.ultimate_soccer_dataset.match_betting_odds"
9  WHERE match_id = '462ae8db-4f46-5685-8051-40a6655198a5'
10    AND bookmaker = 'bet365'
11    AND odds_type = 'pre-match'
12    -- optional: Zeitraum einschränken
13    AND collected_at BETWEEN TIMESTAMP '2025-07-21 17:00:00'
14                        AND TIMESTAMP '2025-09-21 23:59:59'
15)
16SELECT match_id, market, outcome, odds AS opening_odds, collected_at AS opening_collected_at
17FROM ranked
18WHERE rn = 1
19ORDER BY market, outcome;
20