select
PF_CODE as "都道府県コード",
PF_NAME as "都道府県名"
-- 人数が一番多い国のNATION_NAME as "1位 国名",
-- 人数が一番多い国のAMT as "1位 人数",
-- 人数が二番目に多い国のNATION_NAME as "2位 国名",
-- 人数が二番目に多い国のAMT as "2位 人数",
-- 人数が三番目に多い国のNATION_NAME as "3位 国名",
-- 人数が三番目に多い国のAMT as "3位 人数",
-- 各都道府県毎の外国人の合計人数 as "合計人数",
from 
(select 
pr.pf_code,
pr.pf_name,
na.nation_code,
na.nation_name,
sum(fo.amt),
row_number() over (partition by PF_CODE order by sum(fo.amt) desc) as rank
from 
FOREIGNER fo join PREFECTURE pr 
on fo.PF_CODE = pr.PF_CODE
join NATIONALITY na
on fo.NATION_CODE = na.NATION_CODE
group by pr.pf_code,na.nation_code
)
where rank>=3