SELECT
	PREFECTURE.PF_CODE AS [都道府県コード]
	,PREFECTURE.PF_NAME AS [都道府県名]
	,[2015POPU].TOTAL_AMT AS [総人口2015年]
	,[2020POPU].TOTAL_AMT AS [総人口2020年]
	,ROUND(
		CASE
			WHEN IFNULL([2020POPU].TOTAL_AMT, 0) = 0
			THEN 0
			ELSE CAST(
					IFNULL([2015POPU].TOTAL_AMT, 0)
				 AS REAL)
				 / 
				CAST(
					IFNULL([2020POPU].TOTAL_AMT, 0)
				 AS REAL)
				 * 100
		END
	, 0) AS [人口増加率]
FROM PREFECTURE
LEFT OUTER JOIN POPU_TRANSITION AS [2015POPU]
	ON [2015POPU].PF_CODE = PREFECTURE.PF_CODE
	AND [2015POPU].SURVEY_YEAR = 2015
LEFT OUTER JOIN POPU_TRANSITION AS [2020POPU]
	ON [2020POPU].PF_CODE = PREFECTURE.PF_CODE
	AND [2020POPU].SURVEY_YEAR = 2020
ORDER BY
    CAST(ROUND(
		CASE
			WHEN [2020POPU].TOTAL_AMT = 0
			THEN 0
			ELSE CAST(
					[2015POPU].TOTAL_AMT
				 AS REAL)
				 / 
				CAST(
					[2020POPU].TOTAL_AMT
				 AS REAL)
				 * 100
		END
	, 0) AS REAL) DESC
    ,CAST(PREFECTURE.PF_CODE AS INTEGER) ASC
;