with
ranked as (
    select
        pf_code
        ,nation_code
        ,row_number() over(partition by pf_code order by amt desc) as rank
        ,sum(amt) over(partition by pf_code) as total_amt
        ,amt
    from (
        select
            pf_code
            ,nation_code
            ,sum(amt) as amt
        from FOREIGNER
        where NATION_CODE <> 113
        group by
            pf_code
            ,nation_code    
    )
)
select
    a.PF_CODE 都道府県コード
    ,p.PF_NAME 都道府県名
    ,MAX(case when rank = 1 then nation_name end) '1位 国名'
    ,SUM(case when rank = 1 then amt end) '1位 人数'
    ,MAX(case when rank = 2 then nation_name end) '2位 国名'
    ,SUM(case when rank = 2 then amt end) '2位 人数'
    ,MAX(case when rank = 3 then nation_name end) '3位 国名'
    ,SUM(case when rank = 3 then amt end) '3位 人数'
    ,MAX(total_amt) 合計人数
from ranked a
join PREFECTURE p
    on p.pf_code = a.pf_code
join NATIONALITY n
    on n.nation_code = a.nation_code
group by 
    a.PF_CODE 
    ,p.PF_NAME 
order by 合計人数 desc, 都道府県コード