1SELECT
2    to_team.country AS country,
3    
4    COUNT(*) AS total_transfers,
5    
6    COUNT(CASE 
7              WHEN t.transfer_fee_eur IS NULL OR t.transfer_fee_eur = 0 THEN 1 
8         END) AS free_transfers,
9    
10    COUNT(CASE 
11              WHEN t.transfer_fee_eur > 0 THEN 1 
12         END) AS paid_transfers,
13    
14    ROUND(100.0 * COUNT(CASE 
15                            WHEN t.transfer_fee_eur IS NULL OR t.transfer_fee_eur = 0 THEN 1 
16                       END) / COUNT(*), 2) AS pct_free_transfers,
17    
18    ROUND(100.0 * COUNT(CASE 
19                            WHEN t.transfer_fee_eur > 0 THEN 1 
20                       END) / COUNT(*), 2) AS pct_paid_transfers
21
22FROM
23    @blt.ultimate_soccer_dataset.transfers AS t
24JOIN
25    @blt.ultimate_soccer_dataset.teams AS to_team
26    ON t.to_team_id = to_team.team_id
27WHERE
28    EXTRACT(YEAR FROM t.transfer_date) = 2024
29    AND to_team.country IN ('England', 'Spain', 'Italy', 'Germany', 'France')
30GROUP BY
31    to_team.country
32ORDER BY
33    pct_free_transfers DESC