with populations as (
    select
        pf_code,
        nation_code,
        amt,
        rank() over (partition by pf_code order by amt desc, nation_code) as population_rank
    from
        foreigner
    where
        nation_code != '113'
),
top_populations as (
    select
        pf_code,
        sum(case when population_rank = 1 then amt else 0 end) as first,
        max(case when population_rank = 1 then nation_code else null end) as first_nation_code,
        sum(case when population_rank = 2 then amt else 0 end) as second,
        max(case when population_rank = 2 then nation_code else null end) as second_nation_code,
        sum(case when population_rank = 3 then amt else 0 end) as third,
        max(case when population_rank = 3 then nation_code else null end) as third_nation_code,
        sum(amt) as total
    from
        populations
    group BY
        pf_code
)
select
    TP.pf_code as 都道府県コード,
    P.pf_name as 都道府県名,
    N1.nation_name as '1位 国名',
    TP.first as '1位 人数',
    N2.nation_name as '2位 国名',
    TP.second as '2位 人数',
    N3.nation_name as '3位 国名',
    TP.third as '3位 人数',
    TP.total as '合計人数'
from
    top_populations TP
    inner join prefecture P
    on TP.pf_code = P.pf_code
    inner join nationality N1
    on TP.first_nation_code = N1.nation_code
    inner join nationality N2
    on TP.second_nation_code = N2.nation_code
    inner join nationality N3
    on TP.third_nation_code = N3.nation_code
order by
    TP.total desc,
    P.pf_code
;