WITH foreigner_ranking AS (
    SELECT
        FOREIGNER.PF_CODE
        , FOREIGNER.NATION_CODE
        , FOREIGNER.AMT
        , ROW_NUMBER() OVER(
            PARTITION BY 
                FOREIGNER.PF_CODE
            ORDER BY 
                FOREIGNER.AMT DESC
                , FOREIGNER.NATION_CODE ASC
            ) AS ranking
    FROM
        FOREIGNER
    WHERE
        FOREIGNER.NATION_CODE != 113  -- 'その他'以外
)
, pf_code_total_amt AS (
    SELECT
        PF_CODE
        , SUM(AMT) AS total_amt
    FROM
        foreigner_ranking
    GROUP BY
        PF_CODE
)

SELECT
    foreigner_ranking.PF_CODE AS '都道府県コード'
    , PREFECTURE.PF_NAME AS '都道府県名'
    , MAX(CASE WHEN foreigner_ranking.ranking = 1 THEN NATIONALITY.NATION_NAME END) AS '1位 国名'
    , MAX(CASE WHEN foreigner_ranking.ranking = 1 THEN foreigner_ranking.AMT END) AS '1位 人数'
    , MAX(CASE WHEN foreigner_ranking.ranking = 2 THEN NATIONALITY.NATION_NAME END) AS '2位 国名'
    , MAX(CASE WHEN foreigner_ranking.ranking = 2 THEN foreigner_ranking.AMT END) AS '2位 人数'
    , MAX(CASE WHEN foreigner_ranking.ranking = 3 THEN NATIONALITY.NATION_NAME END) AS '3位 国名'
    , MAX(CASE WHEN foreigner_ranking.ranking = 3 THEN foreigner_ranking.AMT END) AS '3位 人数'
    , pf_code_total_amt.total_amt AS '合計人数'
FROM
    foreigner_ranking
INNER JOIN
    pf_code_total_amt ON foreigner_ranking.PF_CODE = pf_code_total_amt.PF_CODE
INNER JOIN
    PREFECTURE ON foreigner_ranking.PF_CODE = PREFECTURE.PF_CODE
INNER JOIN
    NATIONALITY ON foreigner_ranking.NATION_CODE = NATIONALITY.NATION_CODE
GROUP BY
    foreigner_ranking.PF_CODE
ORDER BY
    pf_code_total_amt.total_amt DESC
    , foreigner_ranking.PF_CODE ASC
;