1WITH referee_stats AS (
2    SELECT
3        m.referee_id,
4        m.referee_name,
5        SUM(CASE WHEN m_stats.type = 'Yellow Cards' THEN m_stats.value END) AS yellow_cards,
6        SUM(CASE WHEN m_stats.type = 'Red Cards'    THEN m_stats.value END) AS red_cards,
7        SUM(CASE WHEN m_stats.type = 'Fouls'        THEN m_stats.value END) AS fouls
8    FROM "@blt.ultimate_soccer_dataset.matches" m
9    INNER JOIN "@blt.ultimate_soccer_dataset.match_stats"  m_stats ON m.match_id      = m_stats.match_id
10    INNER JOIN "@blt.ultimate_soccer_dataset.teams"        teams   ON m_stats.team_id  = teams.team_id
11    INNER JOIN "@blt.ultimate_soccer_dataset.seasons"      s       ON m.season_id      = s.season_id
12    INNER JOIN "@blt.ultimate_soccer_dataset.competitions" c       ON s.competition_id = c.competition_id
13    WHERE
14        m.referee_name <> 'None'
15      --  AND YEAR(m.kickoff_timestamp) = 2026
16     --   AND MONTH(m.kickoff_timestamp) IN(3,4,5) 
17    GROUP BY m.referee_id, m.referee_name
18),
19last_match AS (
20    SELECT DISTINCT ON (m.referee_id)
21        m.referee_id,
22        m.home_team_name,
23        m.away_team_name,
24        m.competition_name,
25        c.country                                                             AS competition_country,
26        m.kickoff_timestamp,
27        COALESCE(SUM(CASE WHEN m_stats.type = 'Yellow Cards' THEN m_stats.value END), 0) AS last_yellow_cards,
28        COALESCE(SUM(CASE WHEN m_stats.type = 'Red Cards'    THEN m_stats.value END), 0) AS last_red_cards
29    FROM "@blt.ultimate_soccer_dataset.matches" m
30    INNER JOIN "@blt.ultimate_soccer_dataset.match_stats"  m_stats ON m.match_id      = m_stats.match_id
31    INNER JOIN "@blt.ultimate_soccer_dataset.seasons"      s       ON m.season_id      = s.season_id
32    INNER JOIN "@blt.ultimate_soccer_dataset.competitions" c       ON s.competition_id = c.competition_id
33    WHERE
34        m.referee_id IS NOT NULL
35        AND m.referee_name <> 'None'
36        AND m.status = 'Match Finished'
37        AND YEAR(m.kickoff_timestamp) = 2026
38        AND MONTH(m.kickoff_timestamp) = 5
39    GROUP BY m.referee_id, m.home_team_name, m.away_team_name, m.competition_name, c.country, m.kickoff_timestamp
40    ORDER BY m.referee_id, m.kickoff_timestamp DESC
41)
42SELECT
43    rs.referee_name,
44    COALESCE(rs.yellow_cards, 0) AS yellow_cards,
45    COALESCE(rs.red_cards, 0) AS red_cards,
46    COALESCE(rs.fouls, 0) AS fouls,
47    CASE 
48        WHEN (COALESCE(rs.yellow_cards, 0) + COALESCE(rs.red_cards, 0)) > 0 
49        THEN ROUND((COALESCE(rs.yellow_cards, 0) + COALESCE(rs.red_cards, 0)) / (COALESCE(rs.fouls, 0) + 0.0001), 2)
50        ELSE NULL
51    END AS cards_per_foul,
52    lm.competition_country,
53    lm.competition_name                                  AS current_competition,
54    lm.home_team_name                                    AS last_match_home_team,
55    lm.away_team_name                                    AS last_match_away_team,
56    strftime(lm.kickoff_timestamp, '%Y-%m-%d %H:%M')    AS last_match_datetime,
57    lm.last_yellow_cards,
58    lm.last_red_cards
59FROM referee_stats rs
60LEFT JOIN last_match lm ON rs.referee_id = lm.referee_id
61WHERE fouls>0
62ORDER BY cards_per_foul desc, rs.yellow_cards DESC, rs.red_cards DESC, rs.fouls DESC
63LIMIT 100