select
    t."都道府県コード",
    t."都道府県名",
    max(
        case
            when rank = 1 then t.NATION_NAME
            else null
        end
    ) as "1位 国名",
    max(
        case
            when rank = 1 then t.AMT
            else 0
        end
    ) as "1位 人数",
    max(
        case
            when rank = 2 then t.NATION_NAME
            else null
        end
    ) as " 2位 国名",
    max(
        case
            when rank = 2 then t.AMT
            else 0
        end
    ) as "2位 人数",
    max(
        case
            when rank = 3 then t.NATION_NAME
            else null
        end
    ) as "3位 国名",
    max(
        case
            when rank = 3 then t.AMT
            else 0
        end
    ) as "3位 人数",
    sum(
        case
            when t.AMT is not null then t.AMT
            else 0
        end
    ) as "合計人数"
from
    (
        select
            *,
            PREFECTURE.PF_CODE as "都道府県コード",
            PREFECTURE.PF_NAME as "都道府県名",
            rank() OVER (
                partition by
                    PREFECTURE.PF_CODE
                ORDER BY
                    FOREIGNER.amt desc,
                    FOREIGNER.NATION_CODE ASC
            ) rank
        from
            PREFECTURE
            join FOREIGNER on FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
            and FOREIGNER.NATION_CODE <> "113"
            join NATIONALITY on NATIONALITY.NATION_CODE = FOREIGNER.NATION_CODE
    ) as t
group by
    t."都道府県コード"
order by
    "合計人数" desc,
    "都道府県コード" asc;