1WITH top_league_teams AS (
2    SELECT DISTINCT team_id FROM (
3        SELECT home_team_id AS team_id
4        FROM @blt.ultimate_soccer_dataset.matches
5        WHERE season_id IN (
6            '6cd6410e-da07-5f45-a4be-89418f53ad2a',  -- Bundesliga
7            '4cbe3f9f-0241-5e3d-8430-728850095ccf',  -- Ligue 1
8            '4a21bbb0-2621-5742-a5c1-cfdf5b737ae4',  -- La Liga
9            'e61fcd02-afce-5d2f-9f00-4ae666eb6903',  -- Serie A
10            '9a913a85-6221-5cdf-a353-974d9bde12f9'   -- Premier League
11        )
12        UNION
13        SELECT away_team_id AS team_id
14        FROM @blt.ultimate_soccer_dataset.matches
15        WHERE season_id IN (
16            '6cd6410e-da07-5f45-a4be-89418f53ad2a',
17            '4cbe3f9f-0241-5e3d-8430-728850095ccf',
18            '4a21bbb0-2621-5742-a5c1-cfdf5b737ae4',
19            'e61fcd02-afce-5d2f-9f00-4ae666eb6903',
20            '9a913a85-6221-5cdf-a353-974d9bde12f9'
21        )
22    )
23),
24
25paid_transfers AS (
26    SELECT
27        to_team.country,
28        CASE 
29            WHEN t.transfer_fee_eur < 100000 THEN '<100k'
30            WHEN t.transfer_fee_eur < 500000 THEN '100k-500k'
31            WHEN t.transfer_fee_eur < 1000000 THEN '500k-1M'
32            WHEN t.transfer_fee_eur < 5000000 THEN '1M-5M'
33            WHEN t.transfer_fee_eur < 10000000 THEN '5M-10M'
34            WHEN t.transfer_fee_eur < 20000000 THEN '10M-20M'
35            WHEN t.transfer_fee_eur < 50000000 THEN '20M-50M'
36            ELSE '50M+'
37        END AS fee_bin
38    FROM
39        @blt.ultimate_soccer_dataset.transfers AS t
40    JOIN
41        @blt.ultimate_soccer_dataset.teams AS to_team
42        ON t.to_team_id = to_team.team_id
43    JOIN
44        top_league_teams AS top
45        ON t.to_team_id = top.team_id
46    WHERE
47        t.transfer_fee_eur IS NOT NULL
48        AND t.transfer_fee_eur > 0
49        AND EXTRACT(YEAR FROM t.transfer_date) = 2024
50),
51
52ucl_wins AS (
53    SELECT
54        CASE 
55            WHEN m.home_score > m.away_score THEN home_team.country
56            WHEN m.home_score < m.away_score THEN away_team.country
57            ELSE NULL
58        END AS winning_country,
59        COUNT(*) AS wins
60    FROM
61        @blt.ultimate_soccer_dataset.matches AS m
62    JOIN @blt.ultimate_soccer_dataset.teams AS home_team
63        ON m.home_team_id = home_team.team_id
64    JOIN @blt.ultimate_soccer_dataset.teams AS away_team
65        ON m.away_team_id = away_team.team_id
66    WHERE
67        m.season_id = '1e23c5ed-5bfa-509e-8830-8032140cddd4'  -- 2024/2025 UCL season
68        AND m.home_score IS NOT NULL
69        AND m.away_score IS NOT NULL
70    GROUP BY
71        winning_country
72)
73
74SELECT
75    pt.country,
76    COUNT(CASE WHEN fee_bin = '<100k' THEN 1 END) AS bin_lt_100k,
77    COUNT(CASE WHEN fee_bin = '100k-500k' THEN 1 END) AS bin_100k_500k,
78    COUNT(CASE WHEN fee_bin = '500k-1M' THEN 1 END) AS bin_500k_1M,
79    COUNT(CASE WHEN fee_bin = '1M-5M' THEN 1 END) AS bin_1M_5M,
80    COUNT(CASE WHEN fee_bin = '5M-10M' THEN 1 END) AS bin_5M_10M,
81    COUNT(CASE WHEN fee_bin = '10M-20M' THEN 1 END) AS bin_10M_20M,
82    COUNT(CASE WHEN fee_bin = '20M-50M' THEN 1 END) AS bin_20M_50M,
83    COUNT(CASE WHEN fee_bin = '50M+' THEN 1 END) AS bin_50M_plus,
84    COALESCE(w.wins, 0) AS ucl_wins
85FROM
86    paid_transfers AS pt
87LEFT JOIN
88    ucl_wins AS w
89    ON pt.country = w.winning_country
90GROUP BY
91    pt.country, w.wins
92ORDER BY
93    pt.country
94