WITH rank AS(
    SELECT
        f.PF_CODE,
        PF_NAME,
        NATION_NAME,
        AMT,
        RANK() OVER(
            PARTITION BY f.PF_CODE
            ORDER BY
                AMT DESC
        ) r
    FROM
        FOREIGNER f
        INNER JOIN PREFECTURE p ON f.PF_CODE = p.PF_CODE
        INNER JOIN NATIONALITY n ON f.NATION_CODE = n.NATION_CODE
    WHERE
        f.NATION_CODE <> '113'
)
SELECT
    PF_CODE 都道府県コード,
    PF_NAME 都道府県名,
    MAX(
        CASE
            WHEN r = 1 THEN NATION_NAME
            ELSE NULL
        END
    ) "1位 国名",
    MAX(
        CASE
            WHEN r = 1 THEN AMT
            ELSE NULL
        END
    ) "1位 人数",
    MAX(
        CASE
            WHEN r = 2 THEN NATION_NAME
            ELSE NULL
        END
    ) "2位 国名",
    MAX(
        CASE
            WHEN r = 2 THEN AMT
            ELSE NULL
        END
    ) "2位 人数",
    MAX(
        CASE
            WHEN r = 3 THEN NATION_NAME
            ELSE NULL
        END
    ) "3位 国名",
    MAX(
        CASE
            WHEN r = 3 THEN AMT
            ELSE NULL
        END
    ) "3位 人数",
    SUM(AMT) 合計人数
FROM
    rank
GROUP BY
    PF_CODE
ORDER BY
    SUM(AMT) DESC,
    PF_CODE