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(100.0 * p2020 / p2015)  AS 人口増加率
FROM
  mart
INNER JOIN
  PREFECTURE
ON
  mart.PF_CODE = PREFECTURE.PF_CODE
WHERE
  ROUND(100.0 * p2020 / p2015) >=100
ORDER BY
  人口増加率 DESC,
  CAST(mart.PF_CODE AS int)