-- select
--     p.PF_CODE AS 都道府県コード
--     ,p.PF_NAME AS 都道府県名
--     ,of.
-- FROM
--     PREFECTURE p
-- JOIN
--     (
--         SELECT
--             f.PF_CODE
--             ,f.NATION_CODE
--             ,f.AMT OVER (PARTITION BY f.PF_CODE)
--         FROM
--             FOREIGNER f
--         ORDER BY
--             f.PF_CODE desc, f.AMT desc
--     ) of
SELECT
    p.PF_CODE AS 都道府県コード
    , p.PF_NAME AS 都道府県名
    , MAX(CASE fn.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
    , MAX(CASE fn.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
    , MAX(CASE fn.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
    , MAX(CASE fn.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
    , MAX(CASE fn.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
    , MAX(CASE fn.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
    , SUM(fn.AMT) AS 合計人数
FROM
    PREFECTURE p
JOIN
    (
        SELECT
            f.PF_CODE
            ,f.NATION_CODE
            ,n.NATION_NAME
            ,f.AMT
            ,RANK() OVER (PARTITION BY f.PF_CODE ORDER BY f.AMT desc, PF_CODE desc) as rank
        FROM
            FOREIGNER f
        JOIN
            NATIONALITY n
        ON
            f.NATION_CODE = n.NATION_CODE
        where
            f.NATION_CODE <> '113'
    ) fn
ON
    p.PF_CODE = fn.PF_CODE;