select
   
    aa.PF_CODE  as "都道府県コード",
    bb.PF_NAME  as "都道府県名",
    aa.nt_1     as "1位 国名",
    aa.amt_1    as "1位 人数",
    aa.nt_2     as "2位 国名",
    aa.amt_2    as "2位 人数",
    aa.nt_3     as "3位 国名",
    aa.amt_3    as "3位 人数",
    aa.amt_1 + aa.amt_2 + aa.amt_3 as "合計人数"
from(
    select 
        a.pf_code,
        max(nt_1) as nt_1,
        max(amt_1) as amt_1,
        max(nt_2) as nt_2,
        max(amt_2) as amt_2,
        max(nt_3) as nt_3,
        max(amt_3) as amt_3
    from(
        select
            a.pf_code,
            case when row_number() over(partition by a.pf_code order by a.amt desc) = 1 then b.nation_name else null end as nt_1,
            case when row_number() over(partition by a.pf_code order by a.amt desc) = 1 then a.amt else null end as amt_1,
            case when row_number() over(partition by a.pf_code order by a.amt desc) = 2 then b.nation_name else null end as nt_2,
            case when row_number() over(partition by a.pf_code order by a.amt desc) = 2 then a.amt else null end as amt_2,
            case when row_number() over(partition by a.pf_code order by a.amt desc) = 3 then b.nation_name else null end as nt_3,
            case when row_number() over(partition by a.pf_code order by a.amt desc) = 3 then a.amt else null end as amt_3
        from foreigner as a
        inner join nationality as b
        using(nation_code)
        where a.nation_code <> 113) as a
    group by a.pf_code) as aa
inner join prefecture as bb
using(pf_code)
order by aa.amt_1 + aa.amt_2 + aa.amt_3 desc, aa.pf_code asc;