WITH stage_norm AS (
  SELECT
    m.match_id,
    CASE
      WHEN lower(COALESCE(m.status,'')) LIKE '%semi%'    THEN 'Semi-final'
      WHEN lower(COALESCE(m.status,'')) LIKE '%final%'
           AND lower(COALESCE(m.status,'')) NOT LIKE '%series%'
           AND lower(COALESCE(m.status,'')) NOT LIKE '%play off%'
           AND lower(COALESCE(m.status,'')) NOT LIKE '%qualification%'
           AND lower(COALESCE(m.status,'')) NOT LIKE '%quarter%'     THEN 'Final'
      WHEN lower(COALESCE(m.status,'')) LIKE '%quarter%' THEN 'Quarter-final'
      ELSE NULL
    END AS stage_clean
  FROM @blt.ultimate_soccer_dataset.matches m
  JOIN @blt.ultimate_soccer_dataset.seasons s ON s.season_id = m.season_id
  WHERE s.competition_id IN (
    '39f3b48e-3910-59ea-bc1e-508a45920661',
    '9b83c46c-ff11-5253-8dff-a94770fc76d4'
  )
)
SELECT stage_clean, COUNT(*) AS matches
FROM stage_norm
WHERE stage_clean IS NOT NULL
GROUP BY stage_clean
ORDER BY stage_clean