WITH RankedData AS (
    SELECT
        h.AREA_CODE,
        AREA.AREA_NAME,
        SURVEY_YEAR,
        FOOD_EXP,
        CONSUMPTION_EXP,
        ROUND((1.0*FOOD_EXP / CONSUMPTION_EXP) * 100, 1) AS RATIO,
        DENSE_RANK() OVER (PARTITION BY SURVEY_YEAR ORDER BY ROUND((1.0*FOOD_EXP / CONSUMPTION_EXP) * 100, 1)) AS RANK
    FROM
        HOUSEHOLD_SURVEY h
    INNER JOIN AREA ON h.AREA_CODE = AREA.AREA_CODE
    WHERE
        SURVEY_YEAR IN (2012, 2017, 2022)
)
SELECT
    r2022.AREA_CODE AS CODE,
    r2022.AREA_NAME AS NAME,
    r2022.RANK AS '2022_RANK',
    r2022.RATIO || '%' AS '2022_RATIO',
    r2017.RANK AS '2017_RANK',
    r2017.RATIO || '%' AS '2017_RATIO',
    r2012.RANK AS '2012_RANK',
    r2012.RATIO || '%' AS '2012_RATIO'
FROM
    RankedData r2022
LEFT JOIN
    RankedData r2017 ON r2022.AREA_CODE = r2017.AREA_CODE AND r2017.SURVEY_YEAR = 2017
LEFT JOIN
    RankedData r2012 ON r2022.AREA_CODE = r2012.AREA_CODE AND r2012.SURVEY_YEAR = 2012
WHERE
    r2022.SURVEY_YEAR = 2022
ORDER BY
    r2022.RANK ASC,
    r2022.AREA_CODE DESC;