with log as (
select pf_code,
       nation_code,
       amt,
       rank() over (partition by pf_code order by AMT desc, nation_code) as r
from foreigner
where nation_code != '113'
)

select l.pf_code as '都道府県コード',
       pf_name as '都道府県名',
       max(case when r = 1 then nation_name else '' end) as '1位 国名',
       max(case when r = 1 then amt else 0 end) as '1位 人数',
       max(case when r = 2 then nation_name else '' end) as '2位 国名',
       max(case when r = 2 then amt else 0 end) as '2位 人数',
       max(case when r = 3 then nation_name else '' end) as '3位 国名',
       max(case when r = 3 then amt else 0 end) as '3位 人数',
       sum(amt) as '合計人数'
from log as l
left join nationality as n on l.nation_code = n.nation_code
left join prefecture as p on p.pf_code = l.pf_code
group by 1,2
order by 9 desc, 1