WITH FOREIGN_SUB AS (
    
    SELECT
        P.PF_CODE,
        P.PF_NAME,
        N.NATION_NAME,
        F.AMT,
        RANK() OVER(PARTITION BY P.PF_CODE ORDER BY F.AMT DESC, F.NATION_CODE) AS RANK
    FROM
        FOREIGNER F
        INNER JOIN NATIONALITY N
            ON N.NATION_CODE = F.NATION_CODE
        INNER JOIN PREFECTURE P
            ON P.PF_CODE = F.PF_CODE
    WHERE
        P.PF_CODE != '113'
)

SELECT
    PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    MAX(CASE RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名",
    MAX(CASE RANK WHEN 1 THEN AMT ELSE NULL END) AS "1位 人数",
    MAX(CASE RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名",
    MAX(CASE RANK WHEN 2 THEN AMT ELSE NULL END) AS "2位 人数",
    MAX(CASE RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名",
    MAX(CASE RANK WHEN 3 THEN AMT ELSE NULL END) AS "3位 人数",
    SUM(AMT) AS 合計人数
FROM
    FOREIGN_SUB
GROUP BY
    PF_CODE,
    PF_NAME
ORDER BY
    合計人数 DESC,
    都道府県コード ASC
;