WITH DATA AS (
    SELECT
        FOREIGNER.PF_CODE,
        PREFECTURE.PF_NAME,
        SUM(FOREIGNER.AMT) AS SM,
        NATIONALITY.NATION_CODE,
        NATIONALITY.NATION_NAME,
        RANK() OVER (
            PARTITION BY FOREIGNER.PF_CODE
            ORDER BY SUM(FOREIGNER.AMT) DESC
        ) RNK
    FROM FOREIGNER
    INNER JOIN PREFECTURE ON FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
    INNER JOIN NATIONALITY ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
    WHERE FOREIGNER.NATION_CODE != '113'
    GROUP BY FOREIGNER.PF_CODE, NATIONALITY.NATION_CODE
    ORDER BY FOREIGNER.PF_CODE ASC, SUM(FOREIGNER.AMT) DESC
), RNK1 AS (
    SELECT
        PF_CODE,
        PF_NAME,
        SM,
        NATION_NAME,
        RNK
    FROM DATA
    WHERE RNK = 1
), RNK2 AS (
    SELECT
        PF_CODE,
        PF_NAME,
        SM,
        NATION_NAME,
        RNK
    FROM DATA
    WHERE RNK = 2
), RNK3 AS (
    SELECT
        PF_CODE,
        PF_NAME,
        SM,
        NATION_NAME,
        RNK
    FROM DATA
    WHERE RNK = 3
), SM AS (
    SELECT
        PF_CODE,
        SUM(SM) AS SM
    FROM DATA
    GROUP BY PF_CODE
)

SELECT
    RNK1.PF_CODE AS '都道府県コード',
    RNK1.PF_NAME AS '都道府県名',
    RNK1.NATION_NAME AS '1位 国名',
    RNK1.SM AS '1位 人数',
    RNK2.NATION_NAME AS '2位 国名',
    RNK2.SM AS '2位 人数',
    RNK3.NATION_NAME AS '3位 国名',
    RNK3.SM AS '3位 人数',
    SM.SM AS '合計人数'
FROM RNK1
INNER JOIN RNK2 ON RNK1.PF_CODE = RNK2.PF_CODE
INNER JOIN RNK3 ON RNK1.PF_CODE = RNK3.PF_CODE
INNER JOIN SM ON SM.PF_CODE = RNK1.PF_CODE
ORDER BY 合計人数 DESC, RNK1.PF_CODE DESC
;