WITH TOTAL_AMT_2015 AS(
  SELECT 
    SURVEY_YEAR,
    PF_CODE,
    TOTAL_AMT
  FROM 
    POPU_TRANSITION
  WHERE
    SURVEY_YEAR = 2015
), TOTAL_AMT_2020 AS (
  SELECT 
    SURVEY_YEAR,
    PF_CODE,
    TOTAL_AMT
  FROM 
    POPU_TRANSITION
  WHERE
    SURVEY_YEAR = 2020
)
SELECT 
  pr.PF_CODE AS 都道府県コード,
  pr.PF_NAME AS 都道府県名,
  T2015.TOTAL_AMT AS 総人口2015年,
  T2020.TOTAL_AMT AS 総人口2020年,
  ROUND(
  CAST(T2020.TOTAL_AMT AS REAL) / 
  CAST(T2015.TOTAL_AMT AS REAL)
  * 100) AS 人口増加率
FROM 
  PREFECTURE AS pr  
INNER JOIN 
  TOTAL_AMT_2015 AS T2015
ON T2015.PF_CODE = pr.PF_CODE
INNER JOIN 
  TOTAL_AMT_2020 AS T2020
ON T2020.PF_CODE = pr.PF_CODE
WHERE
  ROUND(
  CAST(T2020.TOTAL_AMT AS REAL) / 
  CAST(T2015.TOTAL_AMT AS REAL)
  * 100) > 100
ORDER BY 
  人口増加率 DESC,
  都道府県コード ASC
  ;