SELECT
    to_team.country AS country,
    
    COUNT(*) AS total_transfers,
    
    COUNT(CASE 
              WHEN t.transfer_fee_eur IS NULL OR t.transfer_fee_eur = 0 THEN 1 
         END) AS free_transfers,
    
    COUNT(CASE 
              WHEN t.transfer_fee_eur > 0 THEN 1 
         END) AS paid_transfers,
    
    ROUND(100.0 * COUNT(CASE 
                            WHEN t.transfer_fee_eur IS NULL OR t.transfer_fee_eur = 0 THEN 1 
                       END) / COUNT(*), 2) AS pct_free_transfers,
    
    ROUND(100.0 * COUNT(CASE 
                            WHEN t.transfer_fee_eur > 0 THEN 1 
                       END) / COUNT(*), 2) AS pct_paid_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
WHERE
    EXTRACT(YEAR FROM t.transfer_date) = 2024
    AND to_team.country IN ('England', 'Spain', 'Italy', 'Germany', 'France')
GROUP BY
    to_team.country
ORDER BY
    pct_free_transfers DESC