SELECT
    PF_CODE AS [都道府県コード],
    PF_NAME AS [都道府県名],
    MAX(CASE WHEN RANK3 = 1 THEN NATION_NAME END) AS [1位 国名],
    MAX(CASE WHEN RANK3 = 1 THEN SUM_AMT     END) AS [1位 人数],
    MAX(CASE WHEN RANK3 = 2 THEN NATION_NAME END) AS [2位 国名],
    MAX(CASE WHEN RANK3 = 2 THEN SUM_AMT     END) AS [2位 人数],
    MAX(CASE WHEN RANK3 = 3 THEN NATION_NAME END) AS [3位 国名],
    MAX(CASE WHEN RANK3 = 3 THEN SUM_AMT     END) AS [3位 人数],
    SUM_ALL AS [合計人数]
FROM
(
    SELECT
        FT.PF_CODE AS PF_CODE,
        PT.PF_NAME AS PF_NAME,
        FT.NATION_CODE AS NATION_CODE,
        NT.NATION_NAME AS NATION_NAME,
        FT.SUM_AMT AS SUM_AMT,
        FT.RANK3 AS RANK3,
        FT.SUM_ALL AS SUM_ALL
    FROM
    (
        SELECT 
            PF_CODE,
            NATION_CODE,
            SUM(AMT) AS SUM_AMT,
            RANK() OVER(PARTITION BY PF_CODE ORDER BY PF_CODE, SUM(AMT) DESC, NATION_CODE) AS RANK3,
            SUM(AMT) OVER(PARTITION BY PF_CODE) AS SUM_ALL
        FROM FOREIGNER
        WHERE
            NATION_CODE != '113'
        GROUP BY 
            PF_CODE,
            NATION_CODE
        ORDER BY 
            PF_CODE,
            SUM(AMT) DESC,
            NATION_CODE
    ) AS FT
    LEFT JOIN PREFECTURE  AS PT on FT.PF_CODE = PT.PF_CODE
    LEFT JOIN NATIONALITY AS NT on FT.NATION_CODE = NT.NATION_CODE
    WHERE RANK3 <= 3
)
GROUP BY
    PF_CODE