WITH ranked_nations AS (
    SELECT 
        PREFECTURE.PF_CODE AS "都道府県コード",
        PREFECTURE.PF_NAME AS "都道府県名",
        NATIONALITY.NATION_NAME,
        FOREIGNER.AMT,
        ROW_NUMBER() OVER (
            PARTITION BY PREFECTURE.PF_CODE 
            ORDER BY FOREIGNER.AMT DESC, NATIONALITY.NATION_CODE ASC
        ) AS rank
    FROM 
        FOREIGNER
    JOIN 
        NATIONALITY ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
    JOIN 
        PREFECTURE ON FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
    WHERE 
        FOREIGNER.NATION_CODE != '113'
),
total_foreigners AS (
    SELECT 
        PREFECTURE.PF_CODE AS "都道府県コード",
        SUM(FOREIGNER.AMT) AS "合計人数"
    FROM 
        FOREIGNER
    JOIN 
        PREFECTURE ON FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
    WHERE 
        FOREIGNER.NATION_CODE != '113'
    GROUP BY 
        PREFECTURE.PF_CODE
)
SELECT 
    ranked_nations."都道府県コード",
    ranked_nations."都道府県名",
    MAX(CASE WHEN rank = 1 THEN NATION_NAME END) AS "1位 国名",
    MAX(CASE WHEN rank = 1 THEN AMT END) AS "1位 人数",
    MAX(CASE WHEN rank = 2 THEN NATION_NAME END) AS "2位 国名",
    MAX(CASE WHEN rank = 2 THEN AMT END) AS "2位 人数",
    MAX(CASE WHEN rank = 3 THEN NATION_NAME END) AS "3位 国名",
    MAX(CASE WHEN rank = 3 THEN AMT END) AS "3位 人数",
    total_foreigners."合計人数"
FROM 
    ranked_nations
JOIN 
    total_foreigners ON ranked_nations."都道府県コード" = total_foreigners."都道府県コード"
GROUP BY 
    ranked_nations."都道府県コード", ranked_nations."都道府県名", total_foreigners."合計人数"
ORDER BY 
    total_foreigners."合計人数" DESC, ranked_nations."都道府県コード" ASC