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