with hiritu AS(
    SELECT SURVEY_YEAR,AREA_CODE,
           ROUND((1.0 * FOOD_EXP / CONSUMPTION_EXP * 100),1) AS RATIO
    FROM HOUSEHOLD_SURVEY
),
RATIORANK AS(
    SELECT AREA_CODE,SURVEY_YEAR,
        DENSE_RANK()OVER(partition by SURVEY_YEAR
                         order by RATIO) AS R,RATIO
    FROM hiritu
)

SELECT DISTINCT BASE.AREA_CODE AS CODE,
                AREA_NAME AS NAME,
       a.R AS "2022_RANK",
       a.RATIO || '%' AS "2022_RATIO",
       b.R AS "2017_RANK",
       b.RATIO || '%' AS "2017_RATIO",
       c.R AS "2012_RANK",
       c.RATIO || '%' AS "2012_RATIO"
FROM RATIORANK BASE JOIN RATIORANK a ON(BASE.AREA_CODE = a.AREA_CODE)
                    JOIN RATIORANK b ON(BASE.AREA_CODE = b.AREA_CODE)
                    JOIN RATIORANK c ON(BASE.AREA_CODE = c.AREA_CODE)
                    JOIN AREA ON(BASE.AREA_CODE = AREA.AREA_CODE)
WHERE a.SURVEY_YEAR = 2022
AND   b.SURVEY_YEAR = 2017
AND   c.SURVEY_YEAR = 2012
ORDER BY "2022_RANK",CODE DESC