WITH W1 AS
(
SELECT HS.AREA_CODE AS CODE
      ,ROUND(CAST(HS.FOOD_EXP AS REAL) / CAST(HS.CONSUMPTION_EXP AS REAL) * 100, 1) AS 'RATIO'
FROM HOUSEHOLD_SURVEY AS HS
WHERE SURVEY_YEAR = '2022'
), W2 AS
(
SELECT HS.AREA_CODE AS CODE
      ,ROUND(CAST(HS.FOOD_EXP AS REAL) / CAST(HS.CONSUMPTION_EXP AS REAL) * 100, 1) AS 'RATIO'
FROM HOUSEHOLD_SURVEY AS HS
WHERE SURVEY_YEAR = '2017'
), W3 AS
(
SELECT HS.AREA_CODE AS CODE
      ,ROUND(CAST(HS.FOOD_EXP AS REAL) / CAST(HS.CONSUMPTION_EXP AS REAL) * 100, 1) AS 'RATIO'
FROM HOUSEHOLD_SURVEY AS HS
WHERE SURVEY_YEAR = '2012'
), W4 AS
(
SELECT W1.CODE AS CODE
      ,DENSE_RANK() OVER(ORDER BY W1.RATIO ASC) AS '2022_RANK'
      ,W1.RATIO || '%' AS '2022_RATIO'
      ,DENSE_RANK() OVER(ORDER BY W2.RATIO ASC) AS '2017_RANK'
      ,W2.RATIO || '%' AS '2017_RATIO'
      ,DENSE_RANK() OVER(ORDER BY W3.RATIO ASC) AS '2012_RANK'
      ,W3.RATIO || '%' AS '2012_RATIO'
FROM W1
JOIN W2
ON W1.CODE = W2.CODE
JOIN W3
ON W1.CODE = W3.CODE
ORDER BY W1.RATIO ASC
        ,W1.CODE DESC
)

SELECT DISTINCT HS.AREA_CODE AS CODE
      ,A.AREA_NAME AS NAME
      ,W4."2022_RANK"
      ,W4."2022_RATIO"
      ,W4."2017_RANK"
      ,W4."2017_RATIO"
      ,W4."2012_RANK"
      ,W4."2012_RATIO"
FROM HOUSEHOLD_SURVEY AS HS
JOIN AREA AS A
ON HS.AREA_CODE = A.AREA_CODE
JOIN W4
ON HS.AREA_CODE = W4.CODE
ORDER BY W4."2022_RANK" ASC
        ,HS.AREA_CODE DESC