WITH RatioData AS (
    SELECT
        hs.SURVEY_YEAR,
        hs.AREA_CODE,
        a.AREA_NAME,
        ROUND((CAST(hs.FOOD_EXP AS REAL) / hs.CONSUMPTION_EXP) * 100, 1) AS RATIO
    FROM
        HOUSEHOLD_SURVEY hs
    JOIN AREA a ON
        hs.AREA_CODE = a.AREA_CODE
    WHERE
        hs.SURVEY_YEAR IN (2012, 2017, 2022)
),
RankedData AS (
    SELECT
        SURVEY_YEAR,
        AREA_CODE,
        AREA_NAME,
        RATIO,
        DENSE_RANK() OVER (PARTITION BY SURVEY_YEAR ORDER BY RATIO ASC) AS RANK
    FROM
        RatioData
)
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, r2022.AREA_CODE DESC;