-- SELECT
--     PF_CODE AS '都道府県コード',
--     PF_NAME AS '都道府県名',
--     MAX(CASE WHEN RANK = 1 THEN NATION_NAME ELSE '' END) AS '1位 国名',
--     SUM(CASE WHEN RANK = 1 THEN AMT ELSE 0 END) AS '1位 人数',
--     MAX(CASE WHEN RANK = 2 THEN NATION_NAME ELSE '' END) AS '2位 国名',
--     SUM(CASE WHEN RANK = 2 THEN AMT ELSE 0 END) AS '2位 人数',
--     MAX(CASE WHEN RANK = 3 THEN NATION_NAME ELSE '' END) AS '3位 国名',
--     SUM(CASE WHEN RANK = 3 THEN AMT ELSE 0 END) AS '3位 人数',
--     SUM(AMT) AS '合計人数'
-- FROM
--     (
--         SELECT 
--             *,
--             ROW_NUMBER() OVER (PARTITION BY f.PF_CODE ORDER BY AMT DESC) AS 'RANK'
--         FROM
--             FOREIGNER f
--         INNER JOIN
--             PREFECTURE p
--         ON
--             f.PF_CODE = p.PF_CODE
--         INNER JOIN
--             NATIONALITY n
--         ON
--             n.NATION_CODE = f.NATION_CODE
-- WHERE
--     f.NATION_CODE <> 113;
--     )
-- GROUP BY NATION_CODE
-- ORDER BY SUM(AMT) DESC, PF_CODE;



SELECT
    PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    MAX(CASE WHEN RANK = 1 THEN NATION_NAME ELSE '' END) AS '1位 国名',
    SUM(CASE WHEN RANK = 1 THEN AMT ELSE 0 END) AS '1位 人数',
    MAX(CASE WHEN RANK = 2 THEN NATION_NAME ELSE '' END) AS '2位 国名',
    SUM(CASE WHEN RANK = 2 THEN AMT ELSE 0 END) AS '2位 人数',
    MAX(CASE WHEN RANK = 3 THEN NATION_NAME ELSE '' END) AS '3位 国名',
    SUM(CASE WHEN RANK = 3 THEN AMT ELSE 0 END) AS '3位 人数',
    SUM(AMT) AS '合計人数'
FROM
(
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY f.PF_CODE ORDER BY AMT DESC) AS 'RANK'
    FROM
        FOREIGNER f
    INNER JOIN
        PREFECTURE p
    ON
        f.PF_CODE = p.PF_CODE
    INNER JOIN
        NATIONALITY n
    ON
        n.NATION_CODE = f.NATION_CODE
    WHERE
        NATION_NAME <> 'その他'
)
GROUP BY
    PF_CODE
ORDER BY
    SUM(AMT) DESC,
    PF_CODE;