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位 人数",
        cc.amt      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 nation_code <> 113) as a
        group by a.pf_code) as aa
    inner join prefecture as bb
    using(pf_code)
    inner join (select pf_code, sum(amt) as amt from foreigner where nation_code <> 113 group by pf_code) as cc
    using(pf_code)
    order by cc.amt desc, aa.pf_code asc;