WITH TBL_2022 as (
SELECT 
AR.AREA_CODE AS CODE ,
AR.AREA_NAME AS NAME,
ROUND((FOOD_EXP * 1.00 / CONSUMPTION_EXP) * 100,1)  as "2022_RATIO",
dense_rank() OVER (order by ROUND((FOOD_EXP * 1.00 / CONSUMPTION_EXP) * 100,1)) as "2022_RANK"
 
FROM 
HOUSEHOLD_SURVEY HS  INNER JOIN AREA AR ON 
HS.AREA_CODE = AR.AREA_CODE
WHERE SURVEY_YEAR = '2022'

) ,

TBL_2017 AS(
SELECT 
AR.AREA_CODE AS CODE ,
AR.AREA_NAME AS NAME,
ROUND((FOOD_EXP * 1.00 / CONSUMPTION_EXP) * 100,1)  as "2017_RATIO",
dense_rank() OVER (order by ROUND((FOOD_EXP * 1.00 / CONSUMPTION_EXP) * 100,1)) as "2017_RANK"
 
FROM 
HOUSEHOLD_SURVEY HS  INNER JOIN AREA AR ON 
HS.AREA_CODE = AR.AREA_CODE
WHERE SURVEY_YEAR = '2017'

) ,
TBL_2012 AS(
SELECT 
AR.AREA_CODE AS CODE ,
AR.AREA_NAME AS NAME,
ROUND((FOOD_EXP * 1.00 / CONSUMPTION_EXP) * 100,1)  as "2012_RATIO",
dense_rank() OVER (order by ROUND((FOOD_EXP * 1.00 / CONSUMPTION_EXP) * 100,1)) as "2012_RANK"
 
FROM 
HOUSEHOLD_SURVEY HS  INNER JOIN AREA AR ON 
HS.AREA_CODE = AR.AREA_CODE
WHERE SURVEY_YEAR = '2012'
) 

SELECT TBL_2022.CODE,TBL_2022.NAME ,"2022_RANK","2022_RATIO" || '%',"2017_RANK","2017_RATIO"|| '%',"2012_RANK","2012_RATIO"|| '%'
FROM TBL_2022 INNER JOIN TBL_2017 ON
TBL_2022.CODE = TBL_2017.CODE
INNER JOIN TBL_2012 ON
TBL_2012.CODE = TBL_2022.CODE
ORDER BY "2022_RANK" ,TBL_2022.CODE DESC