1SELECT
2    to_team.country AS country,
3    SUM(t.transfer_fee_eur) AS total_spent_eur,
4    SUM(CASE 
5            WHEN from_team.country = to_team.country THEN t.transfer_fee_eur 
6            ELSE 0 
7        END) AS domestic_spent_eur,
8    SUM(CASE 
9            WHEN from_team.country != to_team.country THEN t.transfer_fee_eur 
10            ELSE 0 
11        END) AS international_spent_eur,
12    COUNT(*) AS number_of_transfers
13FROM
14    @blt.ultimate_soccer_dataset.transfers AS t
15JOIN
16    @blt.ultimate_soccer_dataset.teams AS to_team
17    ON t.to_team_id = to_team.team_id
18LEFT JOIN
19    @blt.ultimate_soccer_dataset.teams AS from_team
20    ON t.from_team_id = from_team.team_id
21WHERE
22    t.transfer_fee_eur IS NOT NULL
23    AND to_team.country IN ('England', 'Spain', 'Italy', 'Germany', 'France')
24    AND EXTRACT(YEAR FROM t.transfer_date) = 2024
25GROUP BY
26    to_team.country
27ORDER BY
28    total_spent_eur DESC