WITH RatioData AS (
    SELECT 
        HS.AREA_CODE,
        HS.SURVEY_YEAR,
        ROUND((CAST(HS.FOOD_EXP AS REAL) / HS.CONSUMPTION_EXP) * 100, 1) AS RATIO
    FROM 
        HOUSEHOLD_SURVEY HS
    WHERE 
        HS.SURVEY_YEAR IN (2022, 2017, 2012)
), RankedData AS (
    SELECT 
        RD.AREA_CODE,
        RD.SURVEY_YEAR,
        RD.RATIO,
        DENSE_RANK() OVER (PARTITION BY RD.SURVEY_YEAR ORDER BY RD.RATIO ASC, RD.AREA_CODE DESC) AS RANK
    FROM 
        RatioData RD
)
SELECT 
    A.AREA_CODE AS CODE,
    A.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 
    AREA A
LEFT JOIN 
    RankedData R2022 ON A.AREA_CODE = R2022.AREA_CODE AND R2022.SURVEY_YEAR = 2022
LEFT JOIN 
    RankedData R2017 ON A.AREA_CODE = R2017.AREA_CODE AND R2017.SURVEY_YEAR = 2017
LEFT JOIN 
    RankedData R2012 ON A.AREA_CODE = R2012.AREA_CODE AND R2012.SURVEY_YEAR = 2012
ORDER BY 
    "2022_RANK" ASC, A.AREA_CODE DESC
;