WITH pre AS (
    SELECT
        PF_CODE AS 都道府県コード,
        PF_NAME AS 都道府県名,
        NATION_NAME AS 国名,
        NATION_CODE AS 国籍コード,
        AMT AS 人数
    FROM
        FOREIGNER
    LEFT JOIN
        NATIONALITY USING(NATION_CODE)
    LEFT JOIN
        PREFECTURE USING(PF_CODE)
    WHERE
        NATION_CODE != 113
), pre_total AS (
    SELECT
        都道府県コード,
        都道府県名,
        SUM(人数) AS 合計人数
    FROM
        pre
    GROUP BY
        都道府県コード, 都道府県名
), pre_rank AS (
    SELECT
        *,
        RANK () OVER (
            PARTITION BY 都道府県名
            ORDER BY 人数 DESC, 国籍コード ASC
        ) AS rank
    FROM
        pre
), pre_rank1 AS (
    SELECT 
        都道府県コード, 都道府県名, 
        国名 AS "1位 国名",
        人数 AS "1位 人数"
    FROM
        pre_rank
    WHERE
        rank = 1
), pre_rank2 AS (
    SELECT 
        都道府県コード, 都道府県名, 
        国名 AS "2位 国名",
        人数 AS "2位 人数"
    FROM
        pre_rank
    WHERE
        rank = 2
), pre_rank3 AS (
    SELECT 
        都道府県コード, 都道府県名, 
        国名 AS "3位 国名",
        人数 AS "3位 人数"
    FROM
        pre_rank
    WHERE
        rank = 3
)
SELECT * 
FROM pre_rank1
LEFT JOIN pre_rank2 USING(都道府県コード, 都道府県名)
LEFT JOIN pre_rank3 USING(都道府県コード, 都道府県名)
LEFT JOIN pre_total USING(都道府県コード, 都道府県名)
ORDER BY
    合計人数 DESC, 都道府県コード ASC