WITH
  Ratio AS (
    SELECT
      hs.AREA_CODE
    , hs.SURVEY_YEAR
    , ROUND((CAST(hs.FOOD_EXP AS REAL) / hs.CONSUMPTION_EXP) * 100, 1) AS RATIO
    FROM
      HOUSEHOLD_SURVEY hs
    WHERE
      hs.SURVEY_YEAR IN (2012, 2017, 2022)
  )
, Ranked AS (
    SELECT
      r.AREA_CODE
    , r.SURVEY_YEAR
    , r.RATIO
    , DENSE_RANK() OVER (
        PARTITION BY
          r.SURVEY_YEAR
        ORDER BY
          r.RATIO
      ) AS RANK
    FROM
      Ratio r
  )
SELECT
  a.AREA_CODE AS CODE
, a.AREA_NAME AS NAME
, r2022.RANK AS "2022_RANK"
, r2022.RATIO || '%' AS "2022_RATIO"
, r2017.RANK AS "2017_RANK"
, r2017.RATIO || '%' AS "2017_RATIO"
, r2012.RANK AS "2012_RANK"
, r2012.RATIO || '%' AS "2012_RATIO"
FROM
  AREA a
  LEFT JOIN Ranked r2022 ON a.AREA_CODE = r2022.AREA_CODE
  AND r2022.SURVEY_YEAR = 2022
  LEFT JOIN Ranked r2017 ON a.AREA_CODE = r2017.AREA_CODE
  AND r2017.SURVEY_YEAR = 2017
  LEFT JOIN Ranked r2012 ON a.AREA_CODE = r2012.AREA_CODE
  AND r2012.SURVEY_YEAR = 2012
ORDER BY
  "2022_RANK" ASC
, a.AREA_CODE DESC;