WITH records AS (
    SELECT 
        A.AREA_CODE, 
        A.AREA_NAME,
        SURVEY_YEAR,
        QUOTE(ROUND(FOOD_EXP/CAST(CONSUMPTION_EXP AS FLOAT) * 100, 1)) || '%' AS RATIO,
        DENSE_RANK() OVER (PARTITION BY SURVEY_YEAR ORDER BY ROUND(FOOD_EXP/CAST(CONSUMPTION_EXP AS FLOAT) * 100, 1)) RANK
    FROM 
        AREA AS A
    JOIN 
        HOUSEHOLD_SURVEY AS H ON A.AREA_CODE = H.AREA_CODE
    ORDER BY A.AREA_CODE DESC
)

SELECT
    r1.AREA_CODE as CODE, 
    r1.AREA_NAME as NAME, 
    r1.RATIO as '2022_RATIO', 
    r1.RANK as '2022_RANK',
    r2.RATIO as '2017_RATIO', 
    r2.RANK as '2017_RANK',
    r3.RATIO as '2012_RATIO', 
    r3.RANK as '2012_RANK'
FROM records as r1
JOIN (
        SELECT 
            AREA_CODE, RATIO, RANK
        FROM
            records
        WHERE 
            SURVEY_YEAR = 2017
        ORDER BY AREA_CODE DESC
    ) as r2
    ON r1.AREA_CODE = r2.AREA_CODE
JOIN (
    SELECT 
        AREA_CODE, RATIO, RANK
    FROM
        records
    WHERE 
        SURVEY_YEAR = 2012
    ORDER BY AREA_CODE DESC
) as r3
ON r1.AREA_CODE = r3.AREA_CODE
WHERE 
    r1.SURVEY_YEAR = 2022
ORDER BY '2022_RANK'