WITH ranking AS (
    SELECT
        PF_CODE
        , NATION_CODE
        , AMT
        , ROW_NUMBER() OVER(
                PARTITION BY
                    PF_CODE
                ORDER BY
                    AMT DESC
                    , NATION_CODE ASC
            ) AS ranking
    FROM
        FOREIGNER
    WHERE
        NATION_CODE != 113  -- 'その他'以外
    ORDER BY
        PF_CODE
)
, total_amt AS (
    SELECT
        PF_CODE
        , SUM(AMT) AS total_amt
    FROM
        FOREIGNER
    WHERE
        NATION_CODE != 113  -- 'その他'以外
    GROUP BY
        PF_CODE
)
SELECT
    ranking.PF_CODE AS '都道府県コード'
    , PREFECTURE.PF_NAME AS '都道府県名'
    , MAX(CASE WHEN ranking.ranking = 1 THEN NATIONALITY.NATION_NAME END) AS '1位 国名'
    , MAX(CASE WHEN ranking.ranking = 1 THEN ranking.AMT END) AS '1位 人数'
    , MAX(CASE WHEN ranking.ranking = 2 THEN NATIONALITY.NATION_NAME END) AS '2位 国名'
    , MAX(CASE WHEN ranking.ranking = 2 THEN ranking.AMT END) AS '2位 人数'
    , MAX(CASE WHEN ranking.ranking = 3 THEN NATIONALITY.NATION_NAME END) AS '3位 国名'
    , MAX(CASE WHEN ranking.ranking = 3 THEN ranking.AMT END) AS '3位 人数'
    , total_amt.total_amt AS '合計人数'
FROM
    ranking
INNER JOIN
    NATIONALITY ON ranking.NATION_CODE = NATIONALITY.NATION_CODE
INNER JOIN
    PREFECTURE ON ranking.PF_CODE = PREFECTURE.PF_CODE
INNER JOIN
    total_amt ON ranking.PF_CODE = total_amt.PF_CODE
GROUP BY
    ranking.PF_CODE
ORDER BY
    total_amt.total_amt DESC
    , ranking.PF_CODE ASC
;