WITH FILTERED (PF_CODE, NATION_CODE, AMT) AS (
    SELECT
      *
    FROM
        FOREIGNER
    WHERE
        NATION_CODE <> 113
),
RANKED (PF_CODE, NATION_CODE, AMT, R, NATION_NAME) AS (
    SELECT
        PF_CODE,
        FILTERED.NATION_CODE,
        AMT,
        RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, FILTERED.NATION_CODE),
        NATIONALITY.NATION_NAME
    FROM
        FILTERED
        INNER JOIN NATIONALITY ON FILTERED.NATION_CODE = NATIONALITY.NATION_CODE
),
AGG (PF_CODE, AMT) AS (
    SELECT
        PREFECTURE.PF_CODE,
        COALESCE(SUM(AMT), 0) AS AMT
    FROM
        PREFECTURE
        LEFT OUTER JOIN FILTERED ON PREFECTURE.PF_CODE = FILTERED.PF_CODE
    GROUP BY
        PREFECTURE.PF_CODE
)
SELECT
    PREFECTURE.PF_CODE AS 都道府県コード,
    PREFECTURE.PF_NAME AS 都道府県名,
    R1.NATION_NAME AS [1位 国名],
    COALESCE(R1.AMT, 0) AS [1位 人数],
    R2.NATION_NAME AS [2位 国名],
    COALESCE(R2.AMT, 0) AS [2位 人数],
    R3.NATION_NAME AS [3位 国名],
    COALESCE(R3.AMT, 0) AS [3位 人数],
    AGG.AMT AS 合計人数
FROM
    PREFECTURE
    INNER JOIN AGG ON PREFECTURE.PF_CODE = AGG.PF_CODE
    INNER JOIN RANKED R1 ON PREFECTURE.PF_CODE = R1.PF_CODE AND R1.R = 1
    INNER JOIN RANKED R2 ON PREFECTURE.PF_CODE = R2.PF_CODE AND R2.R = 2
    INNER JOIN RANKED R3 ON PREFECTURE.PF_CODE = R3.PF_CODE AND R3.R = 3
ORDER BY
    AGG.AMT DESC,
    PREFECTURE.PF_CODE