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