--SELECT 
--P.PF_CODE AS 都道府県コード, 
--P.PF_NAME AS 都道府県名,
--B.TOTAL_AMT AS 総人口2015年,
--A.TOTAL_AMT AS 総人口2020年,
--ROUND((CAST(A.TOTAL_AMT AS REAL) / CAST(B.TOTAL_AMT AS REAL) * 100)) AS 人口増加率
--FROM  
--(SELECT DISTINCT(PF_CODE) FROM POPU_TRANSITION) T 
--INNER JOIN 
--(SELECT PF_CODE, TOTAL_AMT FROM POPU_TRANSITION WHERE SURVEY_YEAR = 2015) B 
--ON T.PF_CODE = B.PF_CODE INNER JOIN 
--(SELECT PF_CODE, TOTAL_AMT FROM POPU_TRANSITION WHERE SURVEY_YEAR = 2020) A 
--ON T.PF_CODE = A.PF_CODE INNER JOIN 
--PREFECTURE P ON T.PF_CODE = P.PF_CODE 
--WHERE B.TOTAL_AMT < A.TOTAL_AMT
--ORDER BY 人口増加率 DESC, 都道府県コード ASC



SELECT 
P.PF_CODE AS 都道府県コード, 
P.PF_NAME AS 都道府県名,
S.B_TOTAL_AMT AS 総人口2015年,
S.A_TOTAL_AMT AS 総人口2020年,
ROUND((CAST(S.A_TOTAL_AMT AS REAL) / CAST(S.B_TOTAL_AMT AS REAL) * 100)) AS 人口増加率
FROM   
(SELECT B.PF_CODE, B.TOTAL_AMT AS B_TOTAL_AMT, A.TOTAL_AMT AS A_TOTAL_AMT FROM
  (SELECT PF_CODE, TOTAL_AMT FROM POPU_TRANSITION WHERE SURVEY_YEAR = 2015) B 
  INNER JOIN 
  (SELECT PF_CODE, TOTAL_AMT FROM POPU_TRANSITION WHERE SURVEY_YEAR = 2020) A 
  ON B.PF_CODE = A.PF_CODE 
  WHERE B.TOTAL_AMT < A.TOTAL_AMT
) AS S
 INNER JOIN 
PREFECTURE P ON S.PF_CODE = P.PF_CODE 
ORDER BY 人口増加率 DESC, 都道府県コード ASC