with f_agg as (
select 
 pf_code,
 nation_code,
 sum(amt) as amt
from
 foreigner
where
 nation_code!='113'
group by
 1,2
), f_window as (
select
 *,
 nation_name,
 row_number()
   over(partition by fa.pf_code order by fa.amt desc, fa.nation_code asc) as amt_rank,
 sum(amt)
  over(partition by pf_code) as total_amt
from
 f_agg as fa
inner join
 nationality as n
on
 fa.nation_code=n.nation_code
)
select
 f.pf_code as `都道府県コード`,
 p.pf_name as `都道府県名`,
 f1.nation_name as `1位 国名`,
 max(f1.amt) as `1位 人数`,
 f2.nation_name as `2位 国名`,
 max(f2.amt) as `2位 人数`,
 f3.nation_name as `3位 国名`,
 max(f3.amt) as `3位 人数`,
 max(f.total_amt) as `合計人数`
from
 f_window as f
inner join
 prefecture as p
on
 f.pf_code=p.pf_code
inner join
 f_window as f1
on
 f.pf_code=f1.pf_code
 and f1.amt_rank=1
inner join
 f_window as f2
on
 f.pf_code=f2.pf_code
 and f2.amt_rank=2
inner join
 f_window as f3
on
 f.pf_code=f3.pf_code
 and f3.amt_rank=3
group by
 1,2,3
order by
 9 desc, 1 asc