with rank_amt as (
  select 
    f.pf_code,
    p.pf_name,
    f.nation_code,
    n.nation_name,
    f.amt,
    rank() over(partition by p.pf_code order by f.amt desc, f.nation_code asc) as amt_rank
  from
    foreigner f
  inner join prefecture p on f.pf_code = p.pf_code
  inner join nationality n on f.nation_code = n.nation_code
  where
    f.nation_code <> 113
  group by 1, 2, 3, 4
)
select 
  ra.pf_code as 都道府県コード,
  ra.pf_name as 都道府県名,
  max(case when ra.amt_rank = 1 then ra.nation_name end) as '1位 国名',
  max(case when ra.amt_rank = 1 then ra.amt end) as '1位 人数',
  max(case when ra.amt_rank = 2 then ra.nation_name end) as '2位 国名',
  max(case when ra.amt_rank = 2 then ra.amt end) as '2位 人数',
  max(case when ra.amt_rank = 3 then ra.nation_name end) as '3位 国名',
  max(case when ra.amt_rank = 3 then ra.amt end) as '3位 人数', 
  sum(ra.amt) as 合計人数
from rank_amt ra
group by 1,2
order by 合計人数 desc, 都道府県コード asc;