select
    PF_CODE as 都道府県コード
    , PF_NAME as 都道府県名
    ,max(case when rank = 1 then NATION_NAME else null end) as "1位 国名"
    ,max(case when rank = 1 then amt else null end) as "1位 人数"
    ,max(case when rank = 2 then NATION_NAME else null end) as "2位 国名"
    ,max(case when rank = 2 then amt else null end) as "2位 人数"
    ,max(case when rank = 3 then NATION_NAME else null end) as "3位 国名"
    ,max(case when rank = 3 then amt else null end) as "3位 人数"
    , sum(amt) as 合計人数 
from
    PREFECTURE 
    left join ( 
        SELECT
            *
            , row_number() over ( 
                partition by
                    PF_CODE 
                order by
                    AMT desc
                    , NATION_CODE
            ) as rank
        FROM
            FOREIGNER
    ) B using(PF_CODE)
    left join NATIONALITY using (NATION_CODE)
    
    group by PF_CODE
order by
    合計人数 desc