WITH FOREIGNER_TBL AS(
    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"
)
SELECT
    P.PF_CODE
    ,P.PF_NAME
    ,MAX(CASE F.RANK WHEN 1 THEN N.NATION_NAME ELSE NULL END) AS "1位 国名"
    ,MAX(CASE F.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
    ,MAX(CASE F.RANK WHEN 2 THEN N.NATION_NAME ELSE NULL END) AS "2位 国名"
    ,MAX(CASE F.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
    ,MAX(CASE F.RANK WHEN 3 THEN N.NATION_NAME ELSE NULL END) AS "3位 国名"
    ,MAX(CASE F.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
    ,SUM(AMT) AS "合計人数"
FROM
    FOREIGNER_TBL AS F
        INNER JOIN PREFECTURE AS P
            ON F.PF_CODE = P.PF_CODE
        INNER JOIN NATIONALITY AS N
            ON F.NATION_CODE = N.NATION_CODE
GROUP BY
    P.PF_CODE
    ,P.PF_NAME
ORDER BY
    合計人数 DESC
    ,P.PF_CODE