SELECT
    HS.AREA_CODE AS CODE,
    AREA_NAME AS NAME,
    DENSE_RANK()OVER(ORDER BY ROUND(CAST(FOOD_EXP AS REAL)/CAST(CONSUMPTION_EXP AS REAL)*100,1) ASC) AS '2022_RANK',
    ROUND(CAST(FOOD_EXP AS REAL)/CAST(CONSUMPTION_EXP AS REAL)*100,1) || "%" AS '2022_RATIO',
    RANK17 AS '2017_RANK',
    RATIO17 || "%" AS '2017_RATIO',
    RANK12 AS '2012_RANK',
    RATIO12 || "%" AS '2012_RATIO'
FROM
    HOUSEHOLD_SURVEY AS HS
    INNER JOIN AREA
        ON AREA.AREA_CODE=HS.AREA_CODE
        
    INNER JOIN (
        SELECT
            AREA_CODE,
            DENSE_RANK()OVER(ORDER BY ROUND(CAST(FOOD_EXP AS REAL)/CAST(CONSUMPTION_EXP AS REAL)*100,1) ASC) AS RANK17,
            ROUND(CAST(FOOD_EXP AS REAL)/CAST(CONSUMPTION_EXP AS REAL)*100,1) AS RATIO17
        FROM
            HOUSEHOLD_SURVEY
        WHERE
            SURVEY_YEAR=2017
        ) AS SUB17
        ON SUB17.AREA_CODE=HS.AREA_CODE
        
    INNER JOIN (
        SELECT
            AREA_CODE,
            DENSE_RANK()OVER(ORDER BY ROUND(CAST(FOOD_EXP AS REAL)/CAST(CONSUMPTION_EXP AS REAL)*100,1) ASC) AS RANK12,
            ROUND(CAST(FOOD_EXP AS REAL)/CAST(CONSUMPTION_EXP AS REAL)*100,1) AS RATIO12
        FROM
            HOUSEHOLD_SURVEY
        WHERE
            SURVEY_YEAR=2012
        ) AS SUB12
        ON SUB12.AREA_CODE=HS.AREA_CODE
WHERE
    SURVEY_YEAR=2022
ORDER BY
    DENSE_RANK()OVER(ORDER BY ROUND(CAST(FOOD_EXP AS REAL)/CAST(CONSUMPTION_EXP AS REAL)*100,1) ASC) ASC,
    CODE DESC