WITH RankedData AS (
    SELECT
        HOUSEHOLD_SURVEY.AREA_CODE,
        AREA_NAME,
        SURVEY_YEAR,
        ROUND(100.00 * FOOD_EXP / NULLIF(CONSUMPTION_EXP, 0), 1) AS RATIO,
        DENSE_RANK() OVER (PARTITION BY SURVEY_YEAR ORDER BY ROUND(100.00 * FOOD_EXP / NULLIF(CONSUMPTION_EXP, 0), 1)) AS RANK
    FROM
        HOUSEHOLD_SURVEY
    LEFT JOIN
        AREA
    ON
        HOUSEHOLD_SURVEY.AREA_CODE=AREA.AREA_CODE
    WHERE
        SURVEY_YEAR IN (2012, 2017, 2022)
)
SELECT
    a.AREA_CODE AS CODE,
    a.AREA_NAME AS NAME,
    a.RANK AS "2022_RANK",
    a.RATIO || '%' AS "2022_RATIO",
    b.RANK AS "2017_RANK",
    b.RATIO || '%' AS "2017_RATIO",
    c.RANK AS "2012_RANK",
    c.RATIO || '%' AS "2012_RATIO"
FROM
    RankedData a
LEFT JOIN
    RankedData b ON a.AREA_CODE = b.AREA_CODE AND b.SURVEY_YEAR = 2017
LEFT JOIN
    RankedData c ON a.AREA_CODE = c.AREA_CODE AND c.SURVEY_YEAR = 2012
WHERE
    a.SURVEY_YEAR = 2022
ORDER BY
    a.RANK ASC,
    a.AREA_CODE DESC;