WITH RATIO_17 AS (
    SELECT
        AREA_CODE
        ,DENSE_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
        ,DENSE_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
    ,DENSE_RANK() OVER (ORDER BY ROUND((CAST(HS.FOOD_EXP AS REAL) / CAST(HS.CONSUMPTION_EXP AS REAL)) * 100, 1)) 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
ORDER BY
    [2022_RANK] ASC
    ,CODE DESC;