1. Learn
    2. /
  2. Courses
    3. /
  3. Time Series Analysis in PostgreSQL

Exercise

Running average and moving average yearly temperature

As a data scientist at a meteorological research center, you wish to get a time-smoothed average of the yearly temperature in each state as each state's temperature evolves over the years. Then, compare the current year's average temperature with the moving average of yearly temperatures over the previous five years.

A common table expression gives the yearly average of the monthly average temperature for each state. Because there is only one weather station per state, grouping by state is equivalent to grouping by station.

Instructions 1/2

undefined XP
    1
    2
  • Calculate the running average of the yearly_avg temperature over each state; include all previous rows and the current row in the average.