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