1WITH stage_norm AS (
2  SELECT
3    m.match_id,
4    CASE
5      WHEN lower(COALESCE(m.status,'')) LIKE '%semi%'    THEN 'Semi-final'
6      WHEN lower(COALESCE(m.status,'')) LIKE '%final%'
7           AND lower(COALESCE(m.status,'')) NOT LIKE '%series%'
8           AND lower(COALESCE(m.status,'')) NOT LIKE '%play off%'
9           AND lower(COALESCE(m.status,'')) NOT LIKE '%qualification%'
10           AND lower(COALESCE(m.status,'')) NOT LIKE '%quarter%'     THEN 'Final'
11      WHEN lower(COALESCE(m.status,'')) LIKE '%quarter%' THEN 'Quarter-final'
12      ELSE NULL
13    END AS stage_clean
14  FROM @blt.ultimate_soccer_dataset.matches m
15  JOIN @blt.ultimate_soccer_dataset.seasons s ON s.season_id = m.season_id
16  WHERE s.competition_id IN (
17    '39f3b48e-3910-59ea-bc1e-508a45920661',
18    '9b83c46c-ff11-5253-8dff-a94770fc76d4'
19  )
20)
21SELECT stage_clean, COUNT(*) AS matches
22FROM stage_norm
23WHERE stage_clean IS NOT NULL
24GROUP BY stage_clean
25ORDER BY stage_clean
26