Baselight
select m.referee_name
       , sum(case when m_stats.type = 'Yellow Cards' THEN m_stats.value END) as yellow_cards
       , sum(case when m_stats.type = 'Red Cards' THEN m_stats.value END) as red_cards
       , sum(case when m_stats.type = 'Fouls' THEN m_stats.value END) as fouls
from @blt.ultimate_soccer_dataset.matches m
     INNER JOIN @blt.ultimate_soccer_dataset.match_stats m_stats ON (m.match_id = m_stats.match_id)
     INNER JOIN @blt.ultimate_soccer_dataset.teams teams ON (m_stats.team_id = teams.team_id)
     INNER JOIN @blt.ultimate_soccer_dataset.seasons s ON (m.season_id = s.season_id)
     INNER JOIN @blt.ultimate_soccer_dataset.competitions c ON (s.competition_id = c.competition_id)
where referee_name <> 'None'
group by 1
order by yellow_cards desc, red_cards desc, fouls desc 
limit 5
referee_nameyellow_cardsred_cardsfouls
Artur Soares Dias1303517047
Wilton Pereira Sampaio1290537563
Antonio Mateu1256496149
D. Herrera1211775848
Raphael Claus1191626839

Share link

Anyone who has the link will be able to view this.