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