WITH population AS(
SELECT
  SURVEY_YEAR,
  PF_CODE,
  TOTAL_AMT
FROM
  POPU_TRANSITION
WHERE
  SURVEY_YEAR = 2015 OR SURVEY_YEAR = 2020
),
mart AS(
  SELECT
    PF_CODE,
    MAX(CASE WHEN SURVEY_YEAR = 2015 THEN TOTAL_AMT ELSE 0 END) AS p2015,
    MAX(CASE WHEN SURVEY_YEAR = 2020 THEN TOTAL_AMT ELSE 0 END) AS p2020
  FROM
    population
  GROUP BY
    PF_CODE
)

SELECT
  mart.PF_CODE AS 都道府県コード,
  PF_NAME AS 都道府県名,
  p2015 AS 総人口2015年,
  p2020 AS 総人口2020年,
  ROUND(p2020 * 100 / p2015)  AS 人口増加率
FROM
  mart
INNER JOIN
  PREFECTURE
ON
  mart.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
  人口増加率 DESC,
  都道府県コード