-- 都道府県に在住している外国籍の人数を集計した外国人データから、各都道府県で人数が多い国のベスト３を表示しなさい。
-- ただし、国籍コード= '113'（その他）のデータは除いて集計すること。
-- また、集計した人数が同数の場合は、国籍コードの昇順で順位付けを行うこと。

-- 表示項目は以下とする。(エイリアスを使用し→の項目名とする)

-- PF_CODE → 都道府県コード
-- PF_NAME → 都道府県名
-- 人数が一番多い国のNATION_NAME → 1位 国名
-- 人数が一番多い国のAMT → 1位 人数
-- 人数が二番目に多い国のNATION_NAME → 2位 国名
-- 人数が二番目に多い国のAMT → 2位 人数
-- 人数が三番目に多い国のNATION_NAME → 3位 国名
-- 人数が三番目に多い国のAMT → 3位 人数
-- 各都道府県毎の外国人の合計人数 → 合計人数
-- 表示順

-- 合計人数の降順
-- 都道府県コードの昇順

WITH
base AS (
    SELECT
        PF_CODE
        , PF_NAME
        , NATION_CODE
        , NATION_NAME
        , AMT
        , RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE ASC) AS AMT_RANK
    FROM
        FOREIGNER

    LEFT JOIN
        NATIONALITY
    USING (NATION_CODE)
    
    LEFT JOIN
        PREFECTURE
    USING (PF_CODE)

    WHERE
        nation_code != '113'
)
SELECT
    PF_CODE AS "都道府県コード"
    , PF_NAME AS "都道府県名"
    , MAX(CASE WHEN AMT_RANK = 1 THEN NATION_NAME ELSE '' END) AS "1位 国名"
    , SUM(CASE WHEN AMT_RANK = 1 THEN AMT ELSE 0 END) AS "1位 人数"
    , MAX(CASE WHEN AMT_RANK = 2 THEN NATION_NAME ELSE '' END) AS "2位 国名"
    , SUM(CASE WHEN AMT_RANK = 2 THEN AMT ELSE 0 END) AS "2位 人数"
    , MAX(CASE WHEN AMT_RANK = 3 THEN NATION_NAME ELSE '' END) AS "3位 国名"
    , SUM(CASE WHEN AMT_RANK = 3 THEN AMT ELSE 0 END) AS "3位 人数"
    , SUM(AMT) AS "合計人数"
FROM
    base

GROUP BY
    PF_CODE
    , PF_NAME

ORDER BY
    "合計人数" DESC
    , PF_CODE ASC