with tmp as (
select
    f.PF_CODE,
    p.PF_NAME,
    f.NATION_CODE,
    n.NATION_NAME,
    sum(f.AMT) AMT,
    rank() over(partition by f.PF_CODE order by f.AMT desc) RANK
from FOREIGNER f
left join PREFECTURE p
    on f.PF_CODE = p.PF_CODE
left join NATIONALITY n
    on f.NATION_CODE = n.NATION_CODE
where
    f.NATION_CODE != '113'
group by 1, 3
order by 1, 5 desc
)
select
    PF_CODE '都道府県コード',
    PF_NAME '都道府県名',
    max(case when RANK = 1 then NATION_NAME END) '1位 国名',
    max(case when RANK = 1 then AMT END) '1位 人数',
    max(case when RANK = 2 then NATION_NAME END) '2位 国名',
    max(case when RANK = 2 then AMT END) '2位 人数',
    max(case when RANK = 3 then NATION_NAME END) '3位 国名',
    max(case when RANK = 3 then AMT END) '3位 人数',
    sum(AMT) '合計人数'
from tmp
group by 1
order by 9 desc, 1