SELECT
    to_team.country AS country,
    SUM(t.transfer_fee_eur) AS total_spent_eur,
    SUM(CASE 
            WHEN from_team.country = to_team.country THEN t.transfer_fee_eur 
            ELSE 0 
        END) AS domestic_spent_eur,
    SUM(CASE 
            WHEN from_team.country != to_team.country THEN t.transfer_fee_eur 
            ELSE 0 
        END) AS international_spent_eur,
    COUNT(*) AS number_of_transfers
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
LEFT JOIN
    @blt.ultimate_soccer_dataset.teams AS from_team
    ON t.from_team_id = from_team.team_id
WHERE
    t.transfer_fee_eur IS NOT NULL
    AND to_team.country IN ('England', 'Spain', 'Italy', 'Germany', 'France')
    AND EXTRACT(YEAR FROM t.transfer_date) = 2024
GROUP BY
    to_team.country
ORDER BY
    total_spent_eur DESC