1select p.name as player
2       , COUNT(distinct mps.match_id) as Matches
3       , SUM(CASE WHEN mps.goals_scored IS NOT NULL THEN mps.goals_scored ELSE 0 END) as Goals
4       , SUM(CASE WHEN mps.goals_assists IS NOT NULL THEN mps.goals_assists ELSE 0 END) as Assists
5       , SUM(CASE WHEN mps.yellow_cards IS NOT NULL THEN mps.yellow_cards ELSE 0 END) as "Yellow cards"
6       , SUM(CASE WHEN mps.red_cards IS NOT NULL THEN mps.red_cards ELSE 0 END) as "Red cards"
7from @blt.ultimate_soccer_dataset.seasons s
8inner join @blt.ultimate_soccer_dataset.competitions c on (s.competition_id = c.competition_id)
9inner join @blt.ultimate_soccer_dataset.matches m on (m.season_id = s.season_id)
10inner join @blt.ultimate_soccer_dataset.match_player_stats mps on (mps.match_id = m.match_id)
11inner join (select distinct player_id, name from @blt.ultimate_soccer_dataset.players) p on (mps.player_id = p.player_id)
12where slug = 'premier-league' and year(s.start_date) BETWEEN 2015 and 2024
13      and m.status = 'Match Finished'
14GROUP BY ALL
15ORDER BY Goals desc, Assists desc, "Yellow cards" asc, "Red cards" asc
16limit 10