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 
        WHERE
            NATION_CODE != '113'
    ) B 
        using (PF_CODE) 
    inner join NATIONALITY 
        using (NATION_CODE) 
group by
    PF_CODE 
order by
    合計人数 desc
    , 都道府県コード ASC;