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