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

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

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

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

with tmp1 as (
    select
        PF_CODE
        , NATION_CODE
        , NATION_NAME
        , AMT
    from
        FOREIGNER
    left join NATIONALITY
        using(NATION_CODE)
    where
        NATION_CODE != 113
)

, tmp2 as (
    select
        PF_CODE
        , NATION_NAME
        , AMT
        , rank() over (
            partition by
                PF_CODE
            order by
                AMT desc
                , NATION_CODE
        ) as _rank
        , sum(AMT) over (
            partition by
                PF_CODE            
        ) as sum_amt
    from
        tmp1
)

select distinct
    base.PF_CODE as 都道府県コード
    , PF_NAME as 都道府県名
    , r1.NATION_NAME as '1位 国名'
    , r1.AMT as '1位 人数'
    , r2.NATION_NAME as '2位 国名'
    , r2.AMT as '2位 人数'
    , r3.NATION_NAME as '3位 国名'
    , r3.AMT as '3位 人数'
    , base.sum_amt as '合計人数'
from tmp2 as base
left join PREFECTURE
    using(PF_CODE)
left join tmp2 as r1
    on base.PF_CODE = r1.PF_CODE
    and r1._rank = 1
left join tmp2 as r2
    on base.PF_CODE = r2.PF_CODE
    and r2._rank = 1
left join tmp2 as r3
    on base.PF_CODE = r3.PF_CODE
    and r3._rank = 1
order by 9 desc, 1