WITH SUB2022 AS (SELECT HS.AREA_CODE AS CODE,
AREA_NAME AS NAME,
RANK() OVER(ORDER BY ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) DESC) AS RANK2022,
ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) AS RATIO2022
FROM HOUSEHOLD_SURVEY HS
INNER JOIN AREA A
ON HS.AREA_CODE = A.AREA_CODE
AND SURVEY_YEAR = 2022),
SUB2017 AS (SELECT HS.AREA_CODE AS CODE,
AREA_NAME AS NAME,
RANK() OVER(ORDER BY ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) DESC) AS RANK2017,
ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) AS RATIO2017
FROM HOUSEHOLD_SURVEY HS
INNER JOIN AREA A
ON HS.AREA_CODE = A.AREA_CODE
AND SURVEY_YEAR = 2017),
SUB2012 AS (SELECT HS.AREA_CODE AS CODE,
AREA_NAME AS NAME,
RANK() OVER(ORDER BY ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) DESC) AS RANK2012,
ROUND(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) AS RATIO2012
FROM HOUSEHOLD_SURVEY HS
INNER JOIN AREA A
ON HS.AREA_CODE = A.AREA_CODE
AND SURVEY_YEAR = 2012)
SELECT SUB2022.CODE AS CODE, SUB2022.NAME,
RANK2022 AS '2022_RANK', RATIO2022 AS '2022_RATIO',
RANK2017 AS '2017_RANK', RATIO2017 AS '2017_RATIO',
RANK2012 AS '2012_RANK', RATIO2012 AS '2012_RATIO'
FROM SUB2022
INNER JOIN SUB2017
ON SUB2022.CODE = SUB2017.CODE
INNER JOIN SUB2012
ON SUB2022.CODE = SUB2012.CODE
ORDER BY RANK2022 ASC,
CODE DESC