WITH fif_year AS(
    SELECT
      PF_CODE,
      TOTAL_AMT as fifteen_total
    FROM POPU_TRANSITION
    WHERE SURVEY_YEAR = '2015'
),
twe_year AS(
  SELECT
    PF_CODE,
    TOTAL_AMT as twenty_total
    FROM POPU_TRANSITION
    WHERE SURVEY_YEAR = '2020'
),
rate AS(
    SELECT
      f.PF_CODE,
      fifteen_total,
      twenty_total,
      ROUND(
        CAST(twenty_total as REAL) / CAST(fifteen_total as REAL) * 100
        ) as increase_rate
    FROM fif_year as f
      INNER JOIN twe_year as t
      ON f.PF_CODE = t.PF_CODE
    WHERE twenty_total > fifteen_total
)
SELECT
  r.PF_CODE as '都道府県コード',
  PF_NAME as '都道府県名',
  fifteen_total as '総人口2015年',
  twenty_total as '総人口2020年',
  increase_rate as '人口増加率'
FROM rate as r
  INNER JOIN PREFECTURE as p
  ON r.PF_CODE = p.PF_CODE
ORDER BY increase_rate DESC, r.PF_CODE ASC
;