/*
調査年毎に都道府県の人口を集計した人口推移データより、2015年から2020年の期間で人口が増加した都道府県を抽出しなさい。 人口増加率の小数点以下は四捨五入とする。ただし、人口が変化していない都道府県は存在しないことが保証されている。

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

    PF_CODE → 都道府県コード
    PF_NAME → 都道府県名
    2015年のTOTAL_AMT → 総人口2015年
    2020年のTOTAL_AMT → 総人口2020年
    人口増加率を%で表示(小数点以下は四捨五入) → 人口増加率

表示順

    人口増加率の降順
    都道府県コードの昇順
*/
select 
    p.PF_CODE as "都道府県コード", 
    p.PF_NAME as "都道府県名", 
    pre_TOTAL_AMT as "総人口2015年",
    TOTAL_AMT as "総人口2020年",
    round(cast((TOTAL_AMT - pre_TOTAL_AMT) as real) / cast((pre_TOTAL_AMT + TOTAL_AMT) as real) * 100 + 100, 0) as "人口増加率"
    from 
    (
        select
            PF_CODE,
            SURVEY_YEAR, 
            TOTAL_AMT, 
            pre_total_amt,
            case 
                when TOTAL_AMT > pre_total_amt then 'UP'
                when TOTAL_AMT < pre_total_amt then 'DOWN'
                when TOTAL_AMT = pre_total_amt then 'equal'
                else '-'
            end as status
            from(
                select 
                    PF_CODE,
                    SURVEY_YEAR, 
                    TOTAL_AMT, 
                    max(TOTAL_AMT) over (partition by PF_CODE order by SURVEY_YEAR rows between 1 preceding and 1 preceding) as pre_total_amt
                    from POPU_TRANSITION
            )
            where status = 'UP' and SURVEY_YEAR = '2020') as pt
                inner join PREFECTURE as p
                on pt.PF_CODE = p.PF_CODE
    order by "人口増加率" desc, p.PF_CODE asc
;