SELECT
    RANK_FOREIGN_POPULATION_TABLE.PF_CODE AS '都道府県コード'
    , P.PF_NAME AS '都道府県名'
    , MAX(CASE WHEN RANK_FOREIGN_POPULATION_TABLE.RANK = 1 THEN N.NATION_NAME END) AS '1位 国名'
    , MAX(CASE WHEN RANK_FOREIGN_POPULATION_TABLE.RANK = 1 THEN RANK_FOREIGN_POPULATION_TABLE.SUM_AMT END) AS '1位 人数'
    , MAX(CASE WHEN RANK_FOREIGN_POPULATION_TABLE.RANK = 2 THEN N.NATION_NAME END) AS '2位 国名'
    , MAX(CASE WHEN RANK_FOREIGN_POPULATION_TABLE.RANK = 2 THEN RANK_FOREIGN_POPULATION_TABLE.SUM_AMT END) AS '2位 人数'
    , MAX(CASE WHEN RANK_FOREIGN_POPULATION_TABLE.RANK = 3 THEN N.NATION_NAME END) AS '3位 国名'
    , MAX(CASE WHEN RANK_FOREIGN_POPULATION_TABLE.RANK = 3 THEN RANK_FOREIGN_POPULATION_TABLE.SUM_AMT END) AS '3位 人数'
    , TOTAL_FOREIGNER_POPULATION_TABLE.TOTAL_AMT AS '合計人数'
FROM
    (
        SELECT
            rank() OVER Win AS RANK
            , PF_CODE
            , NATION_CODE
            , SUM(AMT) AS SUM_AMT
        FROM
            FOREIGNER
        WHERE
            NATION_CODE != '113'
        GROUP BY
            PF_CODE, NATION_CODE
        WINDOW Win AS (PARTITION BY PF_CODE ORDER BY SUM(AMT) DESC)
    ) AS RANK_FOREIGN_POPULATION_TABLE
INNER JOIN
    (
        SELECT
            PF_CODE
            , SUM(AMT) AS TOTAL_AMT
        FROM
            FOREIGNER
        WHERE
            NATION_CODE != '113'
        GROUP BY
            PF_CODE
    ) AS TOTAL_FOREIGNER_POPULATION_TABLE
ON
    RANK_FOREIGN_POPULATION_TABLE.PF_CODE = TOTAL_FOREIGNER_POPULATION_TABLE.PF_CODE
INNER JOIN
    PREFECTURE AS P
ON
    RANK_FOREIGN_POPULATION_TABLE.PF_CODE = P.PF_CODE
INNER JOIN
    NATIONALITY AS N
ON
    RANK_FOREIGN_POPULATION_TABLE.NATION_CODE = N.NATION_CODE
GROUP BY
    RANK_FOREIGN_POPULATION_TABLE.PF_CODE, P.PF_NAME
ORDER BY
    合計人数 DESC, RANK_FOREIGN_POPULATION_TABLE.PF_CODE