-- Step1 - 都道府県コードごとにランクをつける

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