WITH f AS(
    SELECT
        *
    FROM
        FOREIGNER
    WHERE
        NATION_CODE != '113'
),
pop AS(
    SELECT
        PF_CODE,
        SUM(AMT) AS POP
    FROM
        f
    GROUP BY
        PF_CODE
),
no AS(
    SELECT
        f.PF_CODE,
        p.PF_NAME,
        n.NATION_NAME,
        f.NATION_CODE,
        f.AMT,
        ROW_NUMBER() OVER(
            partition by f.PF_CODE
            order by AMT DESC
        ) AS NUM
    FROM
        f,
        NATIONALITY AS n,
        PREFECTURE AS p
    WHERE
        f.NATION_CODE = n.NATION_CODE AND
        f.PF_CODE = p.PF_CODE
),
no1 AS(
    SELECT
        PF_CODE,
        PF_NAME,
        NATION_NAME,
        AMT
    FROM
        no
    WHERE
        NUM = 1
),
no2 AS(
    SELECT
        PF_CODE,
        PF_NAME,
        NATION_NAME,
        AMT
    FROM
        no
    WHERE
        NUM = 2
),
no3 AS(
    SELECT
        PF_CODE,
        PF_NAME,
        NATION_NAME,
        AMT
    FROM
        no
    WHERE
        NUM = 3
)
SELECT
    p.PF_CODE AS 都道府県コード,
    p.PF_NAME AS 都道府県名,
    no1.NATION_NAME AS '1位 国名',
    no1.AMT AS '1位 人数',
    no2.NATION_NAME AS '2位 国名',
    no2.AMT AS '2位 人数',
    no3.NATION_NAME AS '3位 国名',
    no3.AMT AS '3位 人数',
    pop.POP AS 合計人数
FROM
    PREFECTURE AS p,
    pop
LEFT JOIN no1
    ON  p.PF_CODE = no1.PF_CODE
LEFT JOIN no2
    ON  p.PF_CODE = no2.PF_CODE
LEFT JOIN no3
    ON  p.PF_CODE = no3.PF_CODE
WHERE
    p.PF_CODE = pop.PF_CODE AND
    no1.NATION_NAME IS NOT NULL
ORDER BY
    合計人数 DESC,
    p.PF_CODE ASC