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

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

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

合計人数の降順
都道府県コードの昇順
*/
with first_table as (
    select
        PF_CODE, 
        MAX(AMT) as first
    from FOREIGNER a
    where NATION_CODE <> '113'
    group by PF_CODE
)
, second_table as (
    select
        a.PF_CODE, 
        MAX(AMT) as second
    from FOREIGNER a
    left join first_table b on a.PF_CODE = b.PF_CODE
    where a.AMT <> first AND NATION_CODE <> '113'
    group by a.PF_CODE
)

, third_table as (
    select
        a.PF_CODE, 
        MAX(AMT) as third
    from FOREIGNER a
    left join first_table b on a.PF_CODE = b.PF_CODE
    left join second_table c on a.PF_CODE = c.PF_CODE
    where a.AMT <> first
    AND a.AMT <> second
    AND NATION_CODE <> '113'
    group by a.PF_CODE
)
, sum_pre as(
    select
        sum(AMT) as 合計人数,
        PF_CODE
    from FOREIGNER
    where NATION_CODE <> '113'
    group by PF_CODE
)

select
    a.PF_CODE as 都道府県コード, 
    max(d.PF_NAME) as 都道府県名,
    max(CASE WHEN first = e.AMT THEN NATION_NAME END) as "1位 国名", 
    max(first) as "1位 人数", 
    max(CASE WHEN second = e.AMT THEN NATION_NAME END) as "2位 国名", 
    max(second) as "2位 人数", 
    max(CASE WHEN third = e.AMT THEN NATION_NAME END) as "3位 国名", 
    max(third) as "3位 人数", 
    合計人数
from first_table a
join second_table b on a.PF_CODE = b.PF_CODE
join third_table c on a.PF_CODE = c.PF_CODE
join PREFECTURE d on a.PF_CODE = d.PF_CODE
join FOREIGNER e on a.PF_CODE = d.PF_CODE
join NATIONALITY f on e.NATION_CODE=f.NATION_CODE
join sum_pre g on a.PF_CODE = g.PF_CODE
where first = e.AMT
or second = e.AMT
or third = e.AMT
group by a.PF_CODE
order by 合計人数 desc, 都道府県コード;