with SUB as (
    select
        PF_NAME,
        PF_CODE,
        NATION_CODE,
        NATION_NAME,
        rank() over (
            partition by PF_CODE
            order by
                AMT desc,
                NATION_CODE
        ) as RANK,
        AMT
    from
        FOREIGNER
        inner join NATIONALITY using(NATION_CODE)
        inner join PREFECTURE using(PF_CODE)
    where
        NATION_CODE != "113"
)
select
    PF_CODE as 都道府県コード,
    PF_NAME as 都道府県名,
    max(
        case
            RANK
            when 1 then NATION_NAME
            else NULL
        end
    ) as "1位 国名",
    max(
        case
            RANK
            when 1 then AMT
            else NULL
        end
    ) as "1位 人数",
    max(
        case
            RANK
            when 2 then NATION_NAME
            else 0
        end
    ) as "2位 国名",
    max(
        case
            RANK
            when 2 then AMT
            else NULL
        end
    ) as "2位 人数",
    max(
        case
            RANK
            when 3 then NATION_NAME
            else 0
        end
    ) as "3位 国名",
    max(
        case
            RANK
            when 3 then AMT
            else 0
        end
    ) as "3位 人数",
    sum(AMT) as 合計人数
from
    SUB
group by
    PF_CODE
order by
    合計人数 desc,
    都道府県コード;