WITH AMT_ORDER AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY FR.PF_CODE ORDER BY FR.AMT DESC) AS "順位"
        ,FR.PF_CODE
        ,FR.NATION_CODE
        ,NTL.NATION_NAME
        ,FR.AMT
    FROM
        FOREIGNER FR
    LEFT JOIN
        NATIONALITY NTL ON FR.NATION_CODE = NTL.NATION_CODE
    WHERE
        FR.NATION_CODE != '113'
)
, AO1 AS(
    SELECT * FROM AMT_ORDER AO WHERE "順位" = 1
)
, AO2 AS(
    SELECT * FROM AMT_ORDER AO WHERE "順位" = 2
)
, AO3 AS(
    SELECT * FROM AMT_ORDER AO WHERE "順位" = 3
)
, SUM_AMT AS (
    SELECT PF_CODE, SUM(AMT) AS "合計人数" FROM FOREIGNER  WHERE NATION_CODE != '113' GROUP BY PF_CODE
)
SELECT
    PF.PF_CODE AS "都道府県コード"
    ,PF.PF_NAME AS "都道府県名"
    ,AO1.NATION_NAME AS "1位 国名"
    ,AO1.AMT AS "2位 人数"
    ,AO2.NATION_NAME AS "2位 国名"
    ,AO2.AMT AS "2位 人数"
    ,AO3.NATION_NAME AS "3位 国名"
    ,AO3.AMT AS "3位 人数"
    ,SA."合計人数"
FROM
    PREFECTURE PF
INNER JOIN AO1 ON PF.PF_CODE = AO1.PF_CODE
INNER JOIN AO2 ON PF.PF_CODE = AO2.PF_CODE
INNER JOIN AO3 ON PF.PF_CODE = AO3.PF_CODE
LEFT JOIN SUM_AMT SA ON PF.PF_CODE = SA.PF_CODE
ORDER BY
    "合計人数" DESC
    ,"都道府県コード" ASC