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
52SELECT
53    country,
54    COUNT(CASE WHEN fee_bin = '<100k' THEN 1 END) AS bin_lt_100k,
55    COUNT(CASE WHEN fee_bin = '100k-500k' THEN 1 END) AS bin_100k_500k,
56    COUNT(CASE WHEN fee_bin = '500k-1M' THEN 1 END) AS bin_500k_1M,
57    COUNT(CASE WHEN fee_bin = '1M-5M' THEN 1 END) AS bin_1M_5M,
58    COUNT(CASE WHEN fee_bin = '5M-10M' THEN 1 END) AS bin_5M_10M,
59    COUNT(CASE WHEN fee_bin = '10M-20M' THEN 1 END) AS bin_10M_20M,
60    COUNT(CASE WHEN fee_bin = '20M-50M' THEN 1 END) AS bin_20M_50M,
61    COUNT(CASE WHEN fee_bin = '50M+' THEN 1 END) AS bin_50M_plus
62FROM
63    paid_transfers
64GROUP BY
65    country
66ORDER BY
67    country