with tmp as (
select
    p.pf_code as "都道府県コード"
    ,p.pf_name as "都道府県名"
    ,n.nation_name
    ,n.nation_code
    ,f.amt
    ,row_number() over(partition by p.pf_code order by f.amt desc, n.nation_code asc) "ranking"
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
    n.nation_code <> '113'
)
select
    "都道府県コード"
    ,"都道府県名"
    ,max(case when "ranking" = 1 then nation_name else null end) as "1位 国名"
    ,max(case when "ranking" = 1 then amt else 0 end) as "1位 人数"
    ,max(case when "ranking" = 2 then nation_name else null end) as "2位 国名"
    ,max(case when "ranking" = 2 then amt else 0 end) as "2位 人数"
    ,max(case when "ranking" = 3 then nation_name else null end) as "3位 国名"
    ,max(case when "ranking" = 3 then amt else 0 end) as "3位 人数"
    ,sum(amt) "合計人数"
from
    tmp
group by
    "都道府県コード", "都道府県名"
order by
    "合計人数" desc, "人数" desc, "都道府県コード"
;