with
foreigner_rank as(
select
    f.PF_CODE
    , p.PF_NAME
    , n.NATION_NAME
    , AMT
    , rank()over(
    partition by f.PF_CODE
    order by AMT desc, f.PF_CODE) as rank
from
    FOREIGNER as f
    join
        PREFECTURE as p
    on
        f.PF_CODE = p.PF_CODE
    join
        NATIONALITY as n
    on
        f.NATION_CODE = n.NATION_CODE
)
select
      PF_CODE as 都道府県コード
    , PF_NAME as 都道府県名
    , max(case rank when 1 then NATION_NAME else null end) as '１位国名'
    , max(case rank when 1 then AMT else 0 end) as '１位人数'
    , max(case rank when 2 then NATION_NAME else null end) as '2位国名'
    , max(case rank when 2 then AMT else 0 end) as '2位人数'
    , max(case rank when 3 then NATION_NAME else null end) as '3位国名'
    , max(case rank when 3 then AMT else 0 end) as '3位人数'
    , sum(AMT) as 合計人数
from
    foreigner_rank
group by
    都道府県コード, 都道府県名
order by
    合計人数 desc, 都道府県コード asc;