WITH top_league_teams AS (
SELECT DISTINCT team_id FROM (
SELECT home_team_id AS team_id
FROM @blt.ultimate_soccer_dataset.matches
WHERE season_id IN (
'6cd6410e-da07-5f45-a4be-89418f53ad2a',
'4cbe3f9f-0241-5e3d-8430-728850095ccf',
'4a21bbb0-2621-5742-a5c1-cfdf5b737ae4',
'e61fcd02-afce-5d2f-9f00-4ae666eb6903',
'9a913a85-6221-5cdf-a353-974d9bde12f9'
)
UNION
SELECT away_team_id AS team_id
FROM @blt.ultimate_soccer_dataset.matches
WHERE season_id IN (
'6cd6410e-da07-5f45-a4be-89418f53ad2a',
'4cbe3f9f-0241-5e3d-8430-728850095ccf',
'4a21bbb0-2621-5742-a5c1-cfdf5b737ae4',
'e61fcd02-afce-5d2f-9f00-4ae666eb6903',
'9a913a85-6221-5cdf-a353-974d9bde12f9'
)
)
),
paid_transfers AS (
SELECT
to_team.country,
CASE
WHEN t.transfer_fee_eur < 100000 THEN '<100k'
WHEN t.transfer_fee_eur < 500000 THEN '100k-500k'
WHEN t.transfer_fee_eur < 1000000 THEN '500k-1M'
WHEN t.transfer_fee_eur < 5000000 THEN '1M-5M'
WHEN t.transfer_fee_eur < 10000000 THEN '5M-10M'
WHEN t.transfer_fee_eur < 20000000 THEN '10M-20M'
WHEN t.transfer_fee_eur < 50000000 THEN '20M-50M'
ELSE '50M+'
END AS fee_bin
FROM
@blt.ultimate_soccer_dataset.transfers AS t
JOIN
@blt.ultimate_soccer_dataset.teams AS to_team
ON t.to_team_id = to_team.team_id
JOIN
top_league_teams AS top
ON t.to_team_id = top.team_id
WHERE
t.transfer_fee_eur IS NOT NULL
AND t.transfer_fee_eur > 0
AND EXTRACT(YEAR FROM t.transfer_date) = 2024
)
SELECT
country,
COUNT(CASE WHEN fee_bin = '<100k' THEN 1 END) AS bin_lt_100k,
COUNT(CASE WHEN fee_bin = '100k-500k' THEN 1 END) AS bin_100k_500k,
COUNT(CASE WHEN fee_bin = '500k-1M' THEN 1 END) AS bin_500k_1M,
COUNT(CASE WHEN fee_bin = '1M-5M' THEN 1 END) AS bin_1M_5M,
COUNT(CASE WHEN fee_bin = '5M-10M' THEN 1 END) AS bin_5M_10M,
COUNT(CASE WHEN fee_bin = '10M-20M' THEN 1 END) AS bin_10M_20M,
COUNT(CASE WHEN fee_bin = '20M-50M' THEN 1 END) AS bin_20M_50M,
COUNT(CASE WHEN fee_bin = '50M+' THEN 1 END) AS bin_50M_plus
FROM
paid_transfers
GROUP BY
country
ORDER BY
country