SELECT
    FR1.PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    FR1.NATION_NAME AS '1位 国名',
    FR1.AMT AS '1位 人数',
    FR2.NATION_NAME AS '2位 国名',
    FR2.AMT AS '2位 人数',
    FR3.NATION_NAME AS '3位 国名',
    FR3.AMT AS '3位 人数',
    FR_SUM.SUM AS '合計人数'
FROM
    (
        SELECT
            PF_CODE,
            NATION_NAME,
            AMT
        FROM
            (
                SELECT 
                    *, 
                    RANK()
                        OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE DESC) AS RANK
                FROM
                    FOREIGNER
                WHERE
                    NATION_CODE != '113'
            ) AS FR
            JOIN
                NATIONALITY AS NT
            ON
                NT.NATION_CODE = FR.NATION_CODE
        WHERE FR.RANK = 1
    ) AS FR1
    
    FULL JOIN
        (
            SELECT
                PF_CODE,
                NATION_NAME,
                AMT
            FROM
                (
                    SELECT *, RANK()
                        OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE DESC) AS RANK
                    FROM
                        FOREIGNER
                    WHERE
                        NATION_CODE != '113'
                ) AS FR
                JOIN
                    NATIONALITY AS NT
                ON
                    NT.NATION_CODE = FR.NATION_CODE
            WHERE FR.RANK = 2
        ) AS FR2
    ON
        FR2.PF_CODE = FR1.PF_CODE
        
    FULL OUTER JOIN
        (
            SELECT
                PF_CODE,
                NATION_NAME,
                AMT
            FROM
                (
                    SELECT *, RANK()
                        OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE DESC) AS RANK
                    FROM
                        FOREIGNER
                    WHERE
                        NATION_CODE != '113'
                ) AS FR
                JOIN
                    NATIONALITY AS NT
                ON
                    NT.NATION_CODE = FR.NATION_CODE
            WHERE FR.RANK = 3
        ) AS FR3
    ON
        FR3.PF_CODE = FR1.PF_CODE
        
    JOIN
        (
            SELECT 
                PF_CODE,
                SUM(AMT) AS SUM
            FROM FOREIGNER
            WHERE NATION_CODE != '113'
            GROUP BY PF_CODE
        ) AS FR_SUM
    ON
        FR_SUM.PF_CODE = FR1.PF_CODE
        
    JOIN
        PREFECTURE AS PF
    ON
        PF.PF_CODE = FR1.PF_CODE
        
ORDER BY
    SUM DESC,
    FR1.PF_CODE ASC