WITH y22 AS (
    SELECT
        AREA_CODE AS ac,
        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 RNK
    FROM
        HOUSEHOLD_SURVEY 
    WHERE
        SURVEY_YEAR = '2022'
    GROUP BY
        ac
),
y17 AS (
    SELECT
        AREA_CODE AS ac,
        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 RNK
    FROM
        HOUSEHOLD_SURVEY 
    WHERE
        SURVEY_YEAR = '2017'
    GROUP BY
        ac
),
y12 AS (
    SELECT
        AREA_CODE AS ac,
        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 RNK
    FROM
        HOUSEHOLD_SURVEY 
    WHERE
        SURVEY_YEAR = '2012'
    GROUP BY
        ac
)

SELECT
    h.AREA_CODE AS CODE,
    a.AREA_NAME AS NAME,
    y22.RNK AS '2022_RANK',
    y22.RATIO AS '2022_RATIO',
    y17.RNK AS '2017_RANK',
    y17.RATIO AS '2017_RATIO',
    y12.RNK AS '2012_RANK',
    y12.RATIO AS '2012_RATIO'
FROM
    HOUSEHOLD_SURVEY AS h
    LEFT OUTER JOIN 
        AREA AS a ON h.AREA_CODE = a.AREA_CODE
    LEFT OUTER JOIN 
       y22 ON h.AREA_CODE = y22.ac
    LEFT OUTER JOIN 
       y17 ON h.AREA_CODE = y17.ac
    LEFT OUTER JOIN 
       y12 ON h.AREA_CODE = y12.ac
GROUP BY
    CODE,
    NAME
ORDER BY
    y22.RNK,
    CODE DESC