select 
    p.PF_CODE as "都道府県コード", 
    p.PF_NAME as "都道府県名", 
    pre_TOTAL_AMT as "総人口2015年",
    TOTAL_AMT as "総人口2020年",
    round(cast(TOTAL_AMT as real) / cast(pre_TOTAL_AMT as real) * 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
;