WITH before AS (
  SELECT
    PF_CODE,
    TOTAL_AMT
  FROM
    POPU_TRANSITION
  WHERE
    SURVEY_YEAR = 2015
),
after AS (
  SELECT
    PF_CODE,
    TOTAL_AMT
  FROM
    POPU_TRANSITION
  WHERE
    SURVEY_YEAR = 2020
)
SELECT
  before.PF_CODE AS '都道府県コード',
  pref.PF_NAME AS '都道府県名',
  before.TOTAL_AMT AS '総人口2015年',
  after.TOTAL_AMT AS '総人口2020年',
  ROUND(
    CAST(after.TOTAL_AMT AS REAL) / CAST(before.TOTAL_AMT AS REAL) * 100, 0
    ) AS '人口増加率'
  -- 人口増加率を%で表示(小数点以下は四捨五入) AS '人口増加率'
FROM
  before
LEFT JOIN
  after ON after.PF_CODE = before.PF_CODE
LEFT JOIN
  PREFECTURE AS pref ON pref.PF_CODE = before.PF_CODE
WHERE
  after.TOTAL_AMT > before.TOTAL_AMT
ORDER BY 5 DESC, 1