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