WITH t1 AS(
    SELECT
        *,
        row_number() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS rn
    FROM FOREIGNER
    WHERE nation_code != '113'
)
, t2 AS(
    SELECT
        PF_CODE,
        SUM(CASE rn WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数",
        MAX(CASE rn WHEN 1 THEN NATION_CODE ELSE "" END) AS nation_1,
        SUM(CASE rn WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数",
        MAX(CASE rn WHEN 2 THEN NATION_CODE ELSE "" END) AS nation_2,
        SUM(CASE rn WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数",
        MAX(CASE rn WHEN 3 THEN NATION_CODE ELSE "" END) AS nation_3
    FROM t1
    GROUP BY PF_CODE
)

, t3 AS(
    SELECT PF_CODE, sum(AMT) AS "合計人数"
    FROM t1
    -- WHERE rn < 4
    GROUP BY PF_CODE
)

SELECT 
    pf_code,
    pf_name,
    n1.nation_name AS "1位 国名",
    "1位 人数",
    n2.nation_name AS "2位 国名",
    "2位 人数",
    n3.nation_name AS "3位 国名",
    "3位 人数",
    "合計人数"
FROM t2
LEFT JOIN prefecture using(pf_code)
LEFT JOIN t3 using(pf_code)
LEFT JOIN nationality n1 on t2.nation_1 = n1.nation_code
LEFT JOIN nationality n2 on t2.nation_2 = n2.nation_code
LEFT JOIN nationality n3 on t2.nation_3 = n3.nation_code
ORDER BY "合計人数" DESC, pf_code