select
    f.pf_code as "都道府県コード",
    p.pf_name as "都道府県名",
    max(case when f.rnk = 1 then n.nation_name else null end) as "1位 国名",
    max(case when f.rnk = 1 then f.amt else null end) as "1位 人数",
    max(case when f.rnk = 2 then n.nation_name else null end) as "2位 国名",
    max(case when f.rnk = 2 then f.amt else null end) as "2位 人数",
    max(case when f.rnk = 3 then n.nation_name else null end) as "3位 国名",
    max(case when f.rnk = 3 then f.amt else null end) as "3位 人数",
    sum(f.amt) as "合計人数"
from (
    select 
        pf_code,
        nation_code,
        amt,
        rank()over(partition by pf_code order by amt desc, nation_code asc) as rnk
    from 
        foreigner 
    where 
        nation_code != '113'
) f
left join 
    nationality n 
    on f.nation_code = n.nation_code
left join 
    prefecture p 
    on f.pf_code = p.pf_code
group by 
    f.pf_code
order by 
    "合計人数" desc,
    f.pf_code asc