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