SELECT
    PF_CODE AS "都道府県コード",
    PF_NAME AS "都道府県名",
    MAX(CASE WHEN rank_index = 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名",
    MAX(CASE WHEN rank_index = 1 THEN AMT ELSE NULL END) AS "1位 人数",
    MAX(CASE WHEN rank_index = 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名",
    MAX(CASE WHEN rank_index = 2 THEN AMT ELSE NULL END) AS "2位 人数",
    MAX(CASE WHEN rank_index = 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名",
    MAX(CASE WHEN rank_index = 3 THEN AMT ELSE NULL END) AS "3位 人数",
    SUM(AMT) AS "合計人数"
FROM(
    SELECT
        FOREIGNER.*,
        nation_name,
        PF_NAME,
        AMT,
        RANK() OVER(PARTITION BY FOREIGNER.PF_CODE ORDER BY AMT DESC, FOREIGNER.NATION_CODE) AS rank_index
    FROM
        FOREIGNER
    INNER JOIN NATIONALITY USING(NATION_CODE)
    INNER JOIN PREFECTURE USING(PF_CODE)
    WHERE
        CAST(FOREIGNER.NATION_CODE AS INTEGER) != 113
)
GROUP BY
    PF_CODE
ORDER BY
    "合計人数" DESC,
    PF_CODE ASC