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 FOREIGNER ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE AND FOREIGNER.NATION_CODE <> 113
    GROUP BY
        PREFECTURE.PF_CODE
)
SELECT
    PREFECTURE.PF_CODE AS 都道府県コード,
    PREFECTURE.PF_NAME AS 都道府県名,
    R1.NATION_NAME AS [1位 国名],
    R1.AMT AS [1位人数],
    R2.NATION_NAME AS [2位 国名],
    R2.AMT AS [2位人数],
    R3.NATION_NAME AS [3位 国名],
    R3.AMT AS [3位人数],
    AGG.AMT AS 合計人数
FROM
    PREFECTURE
    INNER JOIN AGG ON PREFECTURE.PF_CODE = AGG.PF_CODE
    LEFT OUTER JOIN RANKED R1 ON PREFECTURE.PF_CODE = R1.PF_CODE AND R1.R = 1
    LEFT OUTER JOIN RANKED R2 ON PREFECTURE.PF_CODE = R2.PF_CODE AND R2.R = 2
    LEFT OUTER JOIN RANKED R3 ON PREFECTURE.PF_CODE = R3.PF_CODE AND R3.R = 3
ORDER BY
    AGG.AMT DESC,
    PREFECTURE.PF_CODE