with base as (
select
pf_code
,NATION_CODE
,AMT
,sum(AMT)over (PARTITION BY pf_code)  as total
,RANK() over (PARTITION BY pf_code order by AMT desc) as ranking
from FOREIGNER
where NATION_CODE != '113'
group by 1,2,3
)
select
PREFECTURE.pf_code as "都道府県コード"
,PREFECTURE.pf_name as  "都道府県名"
,max(case when ranking = 1  then nation_name end) as  "1位 国名"
,max(case when ranking = 1  then AMT end) as  "1位 人数"
,max(case when ranking = 2  then nation_name end) as  "2位 国名"
,max(case when ranking = 2  then AMT end) as  "2位 人数"
,max(case when ranking = 3  then nation_name end) as  "3位 国名"
,max(case when ranking = 3  then AMT end) as  "3位 人数"
,max(case when ranking = 1  then total end) as  "合計人数"
from base 
inner join PREFECTURE on base.pf_code = PREFECTURE.pf_code
inner join NATIONALITY on base.NATION_CODE = NATIONALITY.NATION_CODE
group by 1,2
order by max(case when ranking = 1  then total end) desc, PREFECTURE.pf_code