select
    HS2022.AREA_CODE                                as CODE
    , AR.AREA_NAME                                  as NAME
    , dense_rank() over(order by round((cast(HS2022.FOOD_EXP as real)/cast(HS2022.CONSUMPTION_EXP as real))*100,1)  asc)    as '2022_RANK' 
    , round((cast(HS2022.FOOD_EXP as real)/cast(HS2022.CONSUMPTION_EXP as real))*100,1) || '%' as '2022_RATIO'
    , case 
        when HS2017.SURVEY_YEAR is not null then
            dense_rank() over(order by round((cast(HS2017.FOOD_EXP as real)/cast(HS2017.CONSUMPTION_EXP as real))*100,1)  asc) 
        else
            0
    end  as '2017_RANK' 
    , case 
        when HS2017.SURVEY_YEAR is not null then
            round((cast(HS2017.FOOD_EXP as real)/cast(HS2017.CONSUMPTION_EXP as real))*100,1) || '%'
        else
            0
    end  as '2017_RATIO' 
    , case 
        when HS2012.SURVEY_YEAR is not null then
            dense_rank() over(order by round((cast(HS2012.FOOD_EXP as real)/cast(HS2012.CONSUMPTION_EXP as real))*100,1)  asc) 
        else
            0
    end  as '2012_RANK' 
    , case 
        when HS2012.SURVEY_YEAR is not null then
            round((cast(HS2012.FOOD_EXP as real)/cast(HS2012.CONSUMPTION_EXP as real))*100,1) || '%'
        else
            0
    end  as '2012_RATIO' 
 from HOUSEHOLD_SURVEY as HS2022 
    inner join AREA as AR
        on AR.AREA_CODE = HS2022.AREA_CODE
        
    left join HOUSEHOLD_SURVEY as HS2017
        on HS2017.AREA_CODE = HS2022.AREA_CODE
        and  HS2017.SURVEY_YEAR = 2017
    left join HOUSEHOLD_SURVEY as HS2012
        on HS2012.AREA_CODE = HS2022.AREA_CODE
        and  HS2012.SURVEY_YEAR = 2012
where
    HS2022.SURVEY_YEAR = 2022
order by dense_rank() over(order by round((cast(HS2022.FOOD_EXP as real)/cast(HS2022.CONSUMPTION_EXP as real))*100,1)  asc)   asc
 ,CODE asc