В SQL, что мне добавить ниже, чтобы усреднить глубину по годам?

Попробуйте использовать оконную функцию для подсчета среднего значения по SITE_NAME.

SELECT  
  "SITE_NAME" ,
  "SURVEY_YEAR" ,
  AVG("DEPTH") AS snow_depth,
  round(AVG("DEPTH") over (partition by "SITE_NAME"),2) as annual_average
FROM maine_snow_survey_data
WHERE
  "SURVEY_YEAR">=1984 AND
  "DEPTH">=0 AND
  "SITE_NAME" IS NOT NULL
GROUP BY  
  "SURVEY_YEAR",
  "SITE_NAME",
  "DEPTH"
ORDER BY
  "SITE_NAME";

пытаться,

AVG(DEPTH) OVER (PARTITION BY SITE_NAME, SURVEY_YEAR) AS annual_average

чтобы вычислить среднюю глубину для каждой уникальной комбинации SITE_NAME и SURVEY_YEAR.

SELECT  
  SITE_NAME,
  SURVEY_YEAR,
  AVG(DEPTH) AS snow_depth,
  AVG(DEPTH) OVER (PARTITION BY SITE_NAME, SURVEY_YEAR) AS annual_average
FROM Maine_Snow_Survey_Data
WHERE
  SURVEY_YEAR>=1984 AND
  DEPTH>=0 AND
  SITE_NAME IS NOT NULL
GROUP BY  
  SURVEY_YEAR,
  SITE_NAME
ORDER BY
  SITE_NAME