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

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

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

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

SELECT
    PF_CODE AS "都道府県コード",
    PF_NAME AS "都道府県名",
    MAX(CASE WHEN rank = 1 THEN NATION_NAME END) AS "1位 国名",
    SUM(CASE WHEN rank = 1 THEN sum_amt END) AS "1位 人数",
    MAX(CASE WHEN rank = 2 THEN NATION_NAME END) AS "2位 国名",
    SUM(CASE WHEN rank = 2 THEN sum_amt END) AS "2位 人数",
    MAX(CASE WHEN rank = 3 THEN NATION_NAME END) AS "3位 国名",
    SUM(CASE WHEN rank = 3 THEN sum_amt END) AS "3位 人数",
    SUM(sum_amt) AS "合計人数"
FROM
(
    SELECT
        PF_CODE,
        PF_NAME,
        NATION_CODE,
        NATION_NAME,
        RANK() OVER(PARTITION BY PF_CODE ORDER BY sum_amt DESC, NATION_CODE ASC) AS rank,
        sum_amt
    FROM
    (
        SELECT
            p.PF_CODE,
            p.PF_NAME,
            n.NATION_CODE,
            n.NATION_NAME,
            SUM(f.AMT) sum_amt
        FROM
            FOREIGNER AS f
            INNER JOIN PREFECTURE AS p
                ON p.PF_CODE = f.PF_CODE
            INNER JOIN NATIONALITY AS n
                ON n.NATION_CODE = f.NATION_CODE
        WHERE
            n.NATION_CODE <> "113"
        GROUP BY
            1,2,3,4
    )
)
GROUP BY
    1,2
ORDER BY
    SUM(sum_amt) DESC,
    PF_NAME ASC