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

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

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

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

WITH grp_pref as (
SELECT
    PF_CODE,
    NATION_CODE,
    SUM(AMT) as amt_num,
    ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC) as row_num
FROM 
    FOREIGNER
WHERE
    NATION_CODE != '113'
GROUP BY
    1, 2
ORDER BY
    3 DESC 
)
,first_table as (
SELECT
    p.PF_CODE,
    p.NATION_CODE,
    p.amt_num,
    n.NATION_NAME
FROM
    grp_pref as p
JOIN
    NATIONALITY as n
ON
    p.NATION_CODE = n.NATION_CODE
WHERE
    row_num = 1
)

,sec_table as(
SELECT
    p.PF_CODE,
    p.NATION_CODE,
    p.amt_num,
    n.NATION_NAME
FROM
    grp_pref as p
JOIN
    NATIONALITY as n
ON
    p.NATION_CODE = n.NATION_CODE
WHERE
    row_num = 2
)

,th_table as(
SELECT
    p.PF_CODE,
    p.NATION_CODE,
    p.amt_num,
    n.NATION_NAME
FROM
    grp_pref as p
JOIN
    NATIONALITY as n
ON
    p.NATION_CODE = n.NATION_CODE
WHERE
    row_num = 3
)
, sum_table as (
SELECT
    PF_CODE,
    SUM(amt_num) as sum_num
FROM
    grp_pref
GROUP BY
    1
)

SELECT
    f.PF_CODE as 都道府県コード,
    p.PF_NAME as 都道府県名,
    f.NATION_NAME as "1位 国名",
    f.amt_num as "1位 人数",
    s.NATION_NAME as "2位 国名",
    s.amt_num as "2位 人数",
    t.NATION_NAME as "3位 国名",
    t.amt_num as "3位 人数",
    su.sum_num as 合計人数
FROM
    first_table as f
JOIN
    PREFECTURE as p
ON
    f.PF_CODE = p.PF_CODE
JOIN
    sec_table as s
ON
    s.PF_CODE = f.PF_CODE
JOIN
    th_table  as t
ON
    t.PF_CODE = f.PF_CODE
JOIN
    sum_table as su
ON
    su.PF_CODE = f.PF_CODE
ORDER BY
    9 DESC,
    1 ASC,
    f.NATION_CODE ASC