WITH DEF AS(
    WITH ABC AS(
        SELECT
            *
        FROM
            FOREIGNER AS FR
        JOIN
            NATIONALITY AS NA ON (FR.NATION_CODE = NA.NATION_CODE)
        JOIN
            PREFECTURE AS PR ON (FR.PF_CODE = PR.PF_CODE)
        WHERE
            FR.NATION_CODE <> '113'
        ORDER BY
            FR.PF_CODE ASC
            ,AMT DESC
    )
    SELECT
        PF_CODE AS "都道府県コード"
        ,PF_NAME AS "都道府県名"
        ,ROW_NUMBER() OVER (PARTITION BY PF_NAME) AS "RANKING"
        ,NATION_NAME
        ,AMT
    FROM
        ABC
)
SELECT
    "都道府県コード"
    ,"都道府県名"
    ,MAX(CASE RANKING WHEN 1 THEN "NATION_NAME" ELSE NULL END) AS "1位 国名"
    ,MAX(CASE RANKING WHEN 1 THEN "AMT" ELSE 0 END) AS "1位 人数"
    ,MAX(CASE RANKING WHEN 2 THEN "NATION_NAME" ELSE NULL END) AS "2位 国名"
    ,MAX(CASE RANKING WHEN 2 THEN "AMT" ELSE 0 END) AS "2位 人数"
    ,MAX(CASE RANKING WHEN 3 THEN "NATION_NAME" ELSE NULL END) AS "3位 国名"
    ,MAX(CASE RANKING WHEN 3 THEN "AMT" ELSE 0 END) AS "3位 人数"
    ,SUM(AMT) AS "合計人数"
FROM
    DEF
GROUP BY
    "都道府県名"
ORDER BY
    "合計人数" DESC
    ,"都道府県コード" ASC
;