1select p.name as player
2       , SUM(CASE WHEN mps.red_cards IS NOT NULL THEN mps.red_cards ELSE 0 END) as red_cards
3       , SUM(CASE WHEN mps.yellow_cards IS NOT NULL THEN mps.yellow_cards ELSE 0 END) as yellow_cards
4from @blt.ultimate_soccer_dataset.seasons s
5inner join @blt.ultimate_soccer_dataset.competitions c on (s.competition_id = c.competition_id)
6inner join @blt.ultimate_soccer_dataset.matches m on (m.season_id = s.season_id)
7inner join @blt.ultimate_soccer_dataset.match_player_stats mps on (mps.match_id = m.match_id)
8inner join (SELECT distinct player_id, name from @blt.ultimate_soccer_dataset.players) p on (mps.player_id = p.player_id)
9where slug = 'premier-league' and season_label = '2025/2026' and m.status = 'Match Finished'
10GROUP BY ALL
11ORDER BY yellow_cards desc
12limit 3