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