WITH temp AS
(
    
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY t1.PF_CODE ORDER BY AMT DESC)  AS rank_result,
        t1.PF_CODE,
        AMT,
        t1.NATION_CODE	,
        t2.NATION_NAME AS NATION_NAME,
        t3.PF_NAME AS PF_NAME
    
    FROM 
        FOREIGNER AS t1
    JOIN
        NATIONALITY AS t2
    ON
        t2.NATION_CODE = t1.NATION_CODE
        AND
        t1.NATION_CODE != 113
    JOIN
        PREFECTURE AS t3
    ON
        t1.PF_CODE = t3.PF_CODE

),

temp2 AS
(
SELECT 
PF_CODE AS "都道府県コード",
PF_NAME AS "都道府県名",

    max
    (CASE 
    WHEN
        rank_result = 1
    THEN
        NATION_NAME	
    ELSE
        ""
     END
    ) AS "1位 国名",
       SUM
    (CASE 
    WHEN
        rank_result = 1
    THEN
        AMT	
    ELSE
        0
     END
    ) AS "1位 人数",
    max
    (CASE 
    WHEN
        rank_result = 2
    THEN
        NATION_NAME
    ELSE
        ""
     END
    ) AS "2位 国名",
        SUM
    (CASE 
    WHEN
        rank_result = 2
    THEN
        AMT	
    ELSE
        0
     END
    ) AS "2位 人数",
        MAX
    (CASE 
    WHEN
        rank_result = 3
    THEN
        NATION_NAME	
    ELSE
        ""
     END
    ) AS "3位 国名",
     
    
        SUM
    (CASE 
    WHEN
        rank_result = 3
    THEN
        AMT	
    ELSE
        0
     END
    ) AS "3位 人数"
    FROM 
        temp
    GROUP BY
        PF_NAME
),
sum_table AS(
    SELECT
        SUM(AMT) AS s,
        pf_code
    FROM    
        FOREIGNER
    WHERE
        NATION_CODE != 113
    GROUP BY 
        pf_code


)

SELECT
    t1.*,
    t2.s AS "合計人数"
FROM temp2 AS t1
JOIN
    sum_table AS t2
ON
  t1.都道府県コード  = t2.pf_code
ORDER BY
    ("1位 人数" + "2位 人数" + "3位 人数")
DESC