with population_ranks as (
    select
        F.pf_code,
        F.nation_code,
        N.nation_name,
        F.amt,
        rank() over (partition by F.pf_code order by F.amt desc, F.nation_code) as population_rank
    from
        foreigner F
        inner join nationality N
        on F.nation_code = N.nation_code
    where
        F.nation_code != '113' -- その他
)
select
    PR.pf_code as 都道府県コード,
    P.pf_name as 都道府県名,
    max(case when population_rank = 1 then amt else 0 end) as '1位 人数',
    max(case when population_rank = 1 then nation_name else null end) as '1位 国名',
    max(case when population_rank = 2 then amt else 0 end) as '2位 人数',
    max(case when population_rank = 2 then nation_name else null end) as '2位 国名',
    max(case when population_rank = 3 then amt else 0 end) as '3位 人数',
    max(case when population_rank = 3 then nation_name else null end) as '3位 国名',
    sum(amt) as 合計人数
from
    population_ranks PR
    inner join prefecture P
    on PR.pf_code = P.pf_code
group by
    PR.pf_code
order by
    合計人数 desc, PR.pf_code
;