WITH RoundedRatios AS (
    SELECT
        HS.AREA_CODE,
        A.AREA_NAME,
        HS.SURVEY_YEAR,
        ROUND((HS.FOOD_EXP * 100.0) / HS.CONSUMPTION_EXP, 1) AS ROUNDED_RATIO
    FROM
        HOUSEHOLD_SURVEY HS
    JOIN
        AREA A ON HS.AREA_CODE = A.AREA_CODE
    WHERE
        HS.SURVEY_YEAR IN (2012, 2017, 2022)
),
RankedRatios AS (
    SELECT
        AREA_CODE,
        AREA_NAME,
        SURVEY_YEAR,
        ROUNDED_RATIO,
        DENSE_RANK() OVER (PARTITION BY SURVEY_YEAR ORDER BY ROUNDED_RATIO ASC) AS RANK
    FROM
        RoundedRatios
)
SELECT
    RR2022.AREA_CODE AS CODE,
    RR2022.AREA_NAME AS NAME,
    RR2022.RANK AS "2022_RANK",
    printf("%.1f%%", RR2022.ROUNDED_RATIO) AS "2022_RATIO",
    RR2017.RANK AS "2017_RANK",
    printf("%.1f%%", RR2017.ROUNDED_RATIO) AS "2017_RATIO",
    RR2012.RANK AS "2012_RANK",
    printf("%.1f%%", RR2012.ROUNDED_RATIO) AS "2012_RATIO"
FROM
    RankedRatios RR2022
LEFT JOIN
    RankedRatios RR2017 ON RR2022.AREA_CODE = RR2017.AREA_CODE AND RR2017.SURVEY_YEAR = 2017
LEFT JOIN
    RankedRatios RR2012 ON RR2022.AREA_CODE = RR2012.AREA_CODE AND RR2012.SURVEY_YEAR = 2012
WHERE
    RR2022.SURVEY_YEAR = 2022
ORDER BY
    "2022_RANK" ASC,
    CODE DESC;