WITH RATIO_17 AS (
    SELECT
        AREA_CODE
        ,RANK() OVER (ORDER BY ROUND((CAST(FOOD_EXP AS REAL) / CAST(CONSUMPTION_EXP AS REAL)) * 100, 1)) AS RANK_2017
        ,ROUND((CAST(FOOD_EXP AS REAL) / CAST(CONSUMPTION_EXP AS REAL)) * 100, 1) || '%' AS RATIO_2017
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2017
        )
    ,RATIO_12 AS (
        SELECT
        AREA_CODE
        ,RANK() OVER (ORDER BY ROUND((CAST(FOOD_EXP AS REAL) / CAST(CONSUMPTION_EXP AS REAL)) * 100, 1)) AS RANK_2012
        ,ROUND((CAST(FOOD_EXP AS REAL) / CAST(CONSUMPTION_EXP AS REAL)) * 100, 1) || '%' AS RATIO_2012
    FROM
        HOUSEHOLD_SURVEY
    WHERE
        SURVEY_YEAR = 2012
        )

SELECT
    HS.AREA_CODE AS CODE
    ,A.AREA_NAME AS NAME
    ,RANK() OVER (ORDER BY ROUND((CAST(HS.FOOD_EXP AS REAL) / CAST(HS.CONSUMPTION_EXP AS REAL)) * 100, 1), HS.AREA_CODE DESC) AS '2022_RANK'
    ,ROUND((CAST(HS.FOOD_EXP AS REAL) / CAST(HS.CONSUMPTION_EXP AS REAL)) * 100, 1) || '%' AS '2022_RATIO'
    ,RATIO_17.RANK_2017 AS '2017_RANK'
    ,RATIO_17.RATIO_2017 AS '2017_RATIO'
    ,RATIO_12.RANK_2012 AS '2012_RANK'
    ,RATIO_12.RATIO_2012 AS '2012_RATIO'
FROM
    HOUSEHOLD_SURVEY AS HS
LEFT OUTER JOIN
    AREA AS A
  ON
    HS.AREA_CODE = A.AREA_CODE
INNER JOIN
    RATIO_17
  ON
    HS.AREA_CODE = RATIO_17.AREA_CODE
INNER JOIN
    RATIO_12
  ON
    HS.AREA_CODE = RATIO_12.AREA_CODE
WHERE
    SURVEY_YEAR = 2022;