with wk1 as (
select
    wk2.PF_CODE,
    wk2.NATION_CODE,
    wk2.AMT,
    wk3.NATION_NAME,
    rank() over(partition by wk2.PF_CODE order by AMT desc,wk2.NATION_CODE asc) as num
from
    FOREIGNER as wk2
inner join
    NATIONALITY as wk3
on 
    wk2.NATION_CODE = wk3.NATION_CODE
where
    wk2.NATION_CODE != 113
)
select
    wk1.PF_CODE as "都道府県コード",
    wk4.PF_NAME as "都道府県名",
    max(case when wk1.num = 1 then wk1.NATION_NAME else null end) as "1位 国名",
    sum(case when wk1.num = 1 then wk1.amt else 0 end) as "1位 人数",
    max(case when wk1.num = 2 then wk1.NATION_NAME else null end) as "2位 国名",
    sum(case when wk1.num = 2 then wk1.amt else 0 end) as "2位 人数",
    max(case when wk1.num = 3 then wk1.NATION_NAME else null end) as "3位 国名",
    sum(case when wk1.num = 3 then wk1.amt else 0 end) as "3位 人数",
    sum(wk1.amt) as "合計人数"
from
    wk1
inner join
    PREFECTURE as wk4
on
    wk1.PF_CODE = wk4.PF_CODE
group by 
    wk1.PF_CODE
order by
    合計人数 desc,
    都道府県コード desc
;