WITH RANK_FOR AS(
    SELECT
        PF_CODE,
        NATION_CODE,
        AMT,
        ROW_NUMBER() over (
            PARTITION BY
                PF_CODE 
            ORDER BY
                AMT DESC
        ) AS "RANK"
    FROM
        FOREIGNER
    WHERE
        NATION_CODE <> 113
)
,SUM_FOR AS(
    SELECT
        PF_CODE,
        SUM(AMT) AS SUM_AMT
    FROM
        FOREIGNER
    WHERE
        NATION_CODE <> 113
    GROUP BY
        PF_CODE
)
SELECT
    t1.PF_CODE AS 都道府県コード,
    t3.PF_NAME AS 都道府県名,
    MAX(CASE
        WHEN t1.RANK = 1
            THEN t2.NATION_NAME
        ELSE NULL
        END) AS "1位 国名",
    MAX(CASE
        WHEN t1.RANK = 1
            THEN t1.AMT
        ELSE NULL
        END) AS "1位 人数",
    MAX(CASE
        WHEN t1.RANK = 2
            THEN t2.NATION_NAME
        ELSE NULL
        END) AS "2位 国名",
    MAX(CASE
        WHEN t1.RANK = 2
            THEN t1.AMT
        ELSE NULL
        END) AS "2位 人数",
    MAX(CASE
        WHEN t1.RANK = 3
            THEN t2.NATION_NAME
        ELSE NULL
        END) AS "3位 国名",
    MAX(CASE
        WHEN t1.RANK = 3
            THEN t1.AMT
        ELSE NULL
        END) AS "3位 人数",
    MAX(t4.SUM_AMT) AS 合計人数
FROM
    RANK_FOR AS t1
INNER JOIN NATIONALITY AS t2
    ON t1.NATION_CODE = t2.NATION_CODE
INNER JOIN PREFECTURE AS t3
    ON t1.PF_CODE = t3.PF_CODE
INNER JOIN SUM_FOR AS t4
    ON t1.PF_CODE = t4.PF_CODE
GROUP BY
    都道府県コード,
    都道府県名
ORDER BY
    合計人数 DESC,
    t1.PF_CODE ASC