--調査年(SURVEY_YEAR)が2022年
--消費支出(CONSUMPTION_EXP)に占める食料費(FOOD_EXP)の比率を算出して
--比率の低い順に地域の順位
--比率 = 食料費 ÷ 消費支出 × 100、小数点第2位を四捨五入

WITH T22 AS( 
SELECT H.AREA_CODE AS CODE,A.AREA_NAME AS NAME,
ROUND(H.FOOD_EXP*1.00 / H.CONSUMPTION_EXP*1.00*100,1) || "%" AS "2022_RATIO"
FROM HOUSEHOLD_SURVEY AS H
JOIN AREA AS A
ON H.AREA_CODE = A.AREA_CODE
WHERE H.SURVEY_YEAR = 2022
GROUP BY H.AREA_CODE),

T22A AS(
SELECT CODE,NAME,
DENSE_RANK() OVER(ORDER BY "2022_RATIO" ASC) AS "2022_RANK",
"2022_RATIO"
FROM T22),



T17 AS( 
SELECT H.AREA_CODE AS CODE,A.AREA_NAME AS NAME,
ROUND(H.FOOD_EXP*1.00 / H.CONSUMPTION_EXP*1.00*100,1) || "%" AS "2017_RATIO"
FROM HOUSEHOLD_SURVEY AS H
JOIN AREA AS A
ON H.AREA_CODE = A.AREA_CODE
WHERE H.SURVEY_YEAR = 2017
GROUP BY H.AREA_CODE),

T17A AS(
SELECT CODE,NAME,
DENSE_RANK() OVER(ORDER BY "2017_RATIO" ASC) AS "2017_RANK",
"2017_RATIO"
FROM T17),



T12 AS( 
SELECT H.AREA_CODE AS CODE,A.AREA_NAME AS NAME,
ROUND(H.FOOD_EXP*1.00 / H.CONSUMPTION_EXP*1.00*100,1) || "%" AS "2012_RATIO"
FROM HOUSEHOLD_SURVEY AS H
JOIN AREA AS A
ON H.AREA_CODE = A.AREA_CODE
WHERE H.SURVEY_YEAR = 2012
GROUP BY H.AREA_CODE),

T12A AS(
SELECT CODE,NAME,
DENSE_RANK() OVER(ORDER BY "2012_RATIO" ASC) AS "2012_RANK",
"2012_RATIO"
FROM T12)



SELECT *
FROM T22A
JOIN T17A
ON T22A.CODE = T17A.CODE
JOIN T12A
ON T22A.CODE = T12A.CODE
ORDER BY "2022_RANK" ASC,CODE DESC;

--SELECT *
--FROM T12A;