with PF_CODE_UNIQUE as (
        select
            distinct PF_CODE
        from
            PREFECTURE
    ),
    PF_CODE_2015 as (
        select
            pt.PF_CODE,
            pt.TOTAL_AMT
        from
            POPU_TRANSITION pt,
            PREFECTURE p
        where
            pt.PF_CODE = p.PF_CODE
            and pt.SURVEY_YEAR = 2015
    ),
    PF_CODE_2020 as (
        select
            pt.PF_CODE,
            pt.TOTAL_AMT
        from
            POPU_TRANSITION pt,
            PREFECTURE p
        where
            pt.PF_CODE = p.PF_CODE
            and pt.SURVEY_YEAR = 2020
    ),
    TMP_PF as (
        select
            pt.PF_CODE as PF_CODE,
            p.PF_NAME as PF_NAME,
            pf_2015.TOTAL_AMT as TOTAL_AMT_2015,
            pf_2020.TOTAL_AMT as TOTAL_AMT_2020,
            pf_2020.TOTAL_AMT - pf_2015.TOTAL_AMT as INCREASE,
            round( (
                    cast(pf_2020.TOTAL_AMT as REAL) / cast(pf_2015.TOTAL_AMT as REAL)
                ),
                2
            ) * 100 as LIFT
        from
            PF_CODE_UNIQUE pt,
            PF_CODE_2015 pf_2015,
            PF_CODE_2020 pf_2020,
            PREFECTURE p
        where
            pt.PF_CODE = pf_2015.PF_CODE
            and pt.PF_CODE = pf_2020.PF_CODE
            and pt.PF_CODE = p.PF_CODE
    )
select
    PF_CODE as "都道府県コード",
    PF_NAME as "都道府県名",
    TOTAL_AMT_2015 as "総人口2015年",
    TOTAL_AMT_2020 as "総人口2020年",
    LIFT as "人口増加率"
from TMP_PF
where INCREASE > 0
order by LIFT DESC, PF_CODE ASC;