SELECT
    CODE
    , NAME
    , DENSE_RANK() OVER(ORDER BY `2022_RATIO` ASC) AS '2022_RANK'
    , `2022_RATIO` || '%' AS '2022_RATIO'
    , DENSE_RANK() OVER(ORDER BY `2017_RATIO` ASC) AS '2017_RANK'
    , `2017_RATIO` || '%' AS '2017_RATIO'
    , DENSE_RANK() OVER(ORDER BY `2012_RATIO` ASC) AS '2012_RANK'
    , `2012_RATIO` || '%' AS '2012_RATIO'
FROM (
    SELECT
        AREA_CODE AS CODE
        , AREA_NAME AS NAME
        , ROUND(
            SUM(CASE WHEN SURVEY_YEAR = 2022 THEN FOOD_EXP ELSE 0 END)
            / CAST(SUM(CASE WHEN SURVEY_YEAR = 2022 THEN CONSUMPTION_EXP ELSE 0 END) AS REAL)
            * 100
            , 1
          ) AS '2022_RATIO'
        , ROUND(
            SUM(CASE WHEN SURVEY_YEAR = 2017 THEN FOOD_EXP ELSE 0 END)
            / CAST(SUM(CASE WHEN SURVEY_YEAR = 2017 THEN CONSUMPTION_EXP ELSE 0 END) AS REAL)
            * 100
            , 1
          ) AS '2017_RATIO'
        , ROUND(
            SUM(CASE WHEN SURVEY_YEAR = 2012 THEN FOOD_EXP ELSE 0 END)
            / CAST(SUM(CASE WHEN SURVEY_YEAR = 2012 THEN CONSUMPTION_EXP ELSE 0 END) AS REAL)
            * 100
            , 1
          ) AS '2012_RATIO'
    FROM
        HOUSEHOLD_SURVEY
        INNER JOIN AREA
            USING (AREA_CODE)
    GROUP BY
        AREA_CODE
) AS t
ORDER BY
    `2022_RANK` ASC
    , CODE DESC
;