WITH RANK2022 AS
(SELECT
 AREA_CODE,
 ROUND(cast(FOOD_EXP AS Real)/cast(CONSUMPTION_EXP AS Real) * 100,1) AS RATIO,
 DENSE_RANK() over (order by ROUND(cast(FOOD_EXP AS Real)/cast(CONSUMPTION_EXP AS Real) * 100,1) ASC) AS RANK22
 FROM
 HOUSEHOLD_SURVEY
 WHERE
 SURVEY_YEAR = '2022'
)
, RANK2017 as
(SELECT
 AREA_CODE,
 ROUND(cast(FOOD_EXP AS Real)/cast(CONSUMPTION_EXP AS Real) * 100,1) AS RATIO,
 DENSE_RANK() over (order by ROUND(cast(FOOD_EXP AS Real)/cast(CONSUMPTION_EXP AS Real) * 100,1) ASC) AS RANK17
 FROM
 HOUSEHOLD_SURVEY
 WHERE
 SURVEY_YEAR = '2017'
)
, RANK2012 as
(SELECT
 AREA_CODE,
 ROUND(cast(FOOD_EXP AS Real)/cast(CONSUMPTION_EXP AS Real) * 100,1) AS RATIO,
 DENSE_RANK() over (order by ROUND(cast(FOOD_EXP AS Real)/cast(CONSUMPTION_EXP AS Real) * 100,1) ASC) AS RANK12
 FROM
 HOUSEHOLD_SURVEY
 WHERE
 SURVEY_YEAR = '2012'
)
SELECT
a.AREA_CODE AS CODE,
a.AREA_NAME AS NAME,
R2022.RANK22 AS "2022_RANK",
R2022.RATIO || "%" AS "2022_RATIO",
R2017.RANK17 AS "2017_RANK",
R2017.RATIO || "%" AS "2017_RATIO",
R2012.RANK12 AS "2012_RANK",
R2012.RATIO || "%" AS "2012_RATIO"
FROM
AREA a
LEFT JOIN RANK2022 R2022 ON R2022.AREA_CODE = a.AREA_CODE
LEFT JOIN RANK2017 R2017 ON R2017.AREA_CODE = a.AREA_CODE
LEFT JOIN RANK2012 R2012 ON R2012.AREA_CODE = a.AREA_CODE

ORDER BY R2022.RANK22 ASC ,a.AREA_CODE DESC