SELECT
A.AREA_CODE as CODE
,A.AREA_NAME as NAME
,RES_2022.RANK as '2022_RANK'
,RES_2022.RATIO||'%' as '2022_RATIO'
,RES_2017.RANK as '2017_RANK'
,RES_2017.RATIO||'%' as '2017_RATIO'
,RES_2012.RANK as '2012_RANK'
,RES_2012.RATIO||'%' as '2012_RATIO'
FROM
AREA as A
INNER JOIN
(
    select 
    AREA_CODE   as  CODE
    ,dense_rank() over (order by ROUND(100*(FOOD_EXP*1.0)/(CONSUMPTION_EXP*1.0),1)) as  RANK
    ,ROUND(100*(FOOD_EXP*1.0)/(CONSUMPTION_EXP*1.0),1)   as  RATIO
    from HOUSEHOLD_SURVEY
    where SURVEY_YEAR='2022'
)RES_2022
ON
A.AREA_CODE=RES_2022.CODE
INNER JOIN
(
    select 
    AREA_CODE   as  CODE
    ,dense_rank() over (order by ROUND(100*(FOOD_EXP*1.0)/(CONSUMPTION_EXP*1.0),1)) as  RANK
    ,ROUND(100*(FOOD_EXP*1.0)/(CONSUMPTION_EXP*1.0),1)   as  RATIO
    from HOUSEHOLD_SURVEY
    where SURVEY_YEAR='2017'
)RES_2017
ON
A.AREA_CODE=RES_2017.CODE
INNER JOIN
(
    select 
    AREA_CODE   as  CODE
    ,dense_rank() over (order by ROUND(100*(FOOD_EXP*1.0)/(CONSUMPTION_EXP*1.0),1)) as  RANK
    ,ROUND(100*(FOOD_EXP*1.0)/(CONSUMPTION_EXP*1.0),1)   as  RATIO
    from HOUSEHOLD_SURVEY
    where SURVEY_YEAR='2012'
)RES_2012
ON
A.AREA_CODE=RES_2012.CODE
ORDER BY 
RES_2022.RANK
,A.AREA_CODE desc