WITH GHI AS(
    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位 人数"
    FROM
        DEF
    GROUP BY
        "都道府県名"
)
SELECT
    "都道府県コード"
    ,"都道府県名"
    ,"1位 国名"
    ,"1位 人数"
    ,"2位 国名"
    ,"2位 人数"
    ,"3位 国名"
    ,"3位 人数"
    ,("1位 人数"+"2位 人数"+"3位 人数") AS "合計人数"
FROM
    GHI
ORDER BY
    "合計人数" DESC
    ,"都道府県コード" ASC
;