-- 都道府県別の外国籍の人数ランキング
WITH total_amt AS (
    SELECT
        PF_CODE
        , SUM(AMT) AS total_amt
    FROM
        FOREIGNER
    WHERE
        FOREIGNER.NATION_CODE != 113    -- 'その他'以外
    GROUP BY
        PF_CODE
)
, amt_ranking AS (
    SELECT
        FOREIGNER.PF_CODE
        , PREFECTURE.PF_NAME
        -- , FOREIGNER.NATION_CODE -- DEBUG
        , NATIONALITY.NATION_NAME
        , FOREIGNER.AMT
        -- パーティション毎に順位付け（同率の場合同じ順位になり、その次は順位を飛ばす）
        -- , RANK() OVER(PARTITION BY FOREIGNER.PF_CODE ORDER BY FOREIGNER.AMT DESC) AS ranking
        -- パーティション毎に順位付け（同率の場合同じ順位になり、その次の順位を飛ばさない）
        -- , DENSE_RANK() OVER(PARTITION BY FOREIGNER.PF_CODE ORDER BY FOREIGNER.AMT DESC) AS ranking
        -- パーティション毎に順位付け※番号振り（同率の場合でも同じ順位にならない）
        , ROW_NUMBER() OVER(PARTITION BY FOREIGNER.PF_CODE ORDER BY FOREIGNER.AMT DESC, FOREIGNER.NATION_CODE ASC) AS ranking
    FROM
        FOREIGNER
    INNER JOIN
        PREFECTURE ON FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
    INNER JOIN
        NATIONALITY ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
    WHERE
        FOREIGNER.NATION_CODE != 113    -- 'その他'以外
)        
, first_amt AS (
    SELECT
        PF_CODE
        , PF_NAME AS first_pf_name
        , NATION_NAME AS first_nation_name
        , AMT AS first_amt
    FROM
        amt_ranking
    WHERE
        ranking = 1     -- 1位のみ取得
)
, second_amt AS (
    SELECT
        PF_CODE
        , PF_NAME AS second_pf_name
        , NATION_NAME AS second_nation_name
        , AMT AS second_amt
    FROM
        amt_ranking
    WHERE
        ranking = 2     -- 2位のみ取得
)
, third_amt AS (
    SELECT
        PF_CODE
        , PF_NAME AS third_pf_name
        , NATION_NAME AS third_nation_name
        , AMT AS third_amt
    FROM
        amt_ranking
    WHERE
        ranking = 3     -- 3位のみ取得
)
SELECT
    first_amt.PF_CODE AS '都道府県コード'
    , first_amt.first_pf_name AS '都道府県名'
    , first_amt.first_nation_name AS '1位 国名'
    , first_amt.first_amt AS '1位 人数'
    , second_amt.second_nation_name AS '2位 国名'
    , second_amt.second_amt AS '2位 人数'
    , third_amt.third_nation_name AS '3位 国名'
    , third_amt.third_amt AS '3位 人数'
    , total_amt.total_amt AS '合計人数'
FROM
    first_amt
INNER JOIN
    second_amt ON first_amt.PF_CODE = second_amt.PF_CODE
INNER JOIN
    third_amt ON first_amt.PF_CODE = third_amt.PF_CODE
INNER JOIN
    total_amt ON first_amt.PF_CODE = total_amt.PF_CODE
ORDER BY
    total_amt.total_amt DESC
    , first_amt.PF_CODE ASC
;