Get startedGet started for free

Calculate yearly average temperature

As a data analyst you may be called upon by a colleague to perform an ad hoc query that helps them perform their role more effectively or to help satisfy their curiosity about data that is important to them. In this scenario, you are a data scientist at a weather station. The weather station manager wants you to calculate the yearly average temperature for each weather station, by averaging the monthly average.

This exercise is part of the course

Time Series Analysis in PostgreSQL

View Course

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Yearly average of monthly average temp for each station
SELECT 
	station_id, 
	-- Truncate year_month to year
    ___(___, year_month) AS year, 
    ___(t_monthly_avg) AS yearly_avg
FROM temperatures_monthly
WHERE t_monthly_avg > -9999
GROUP BY station_id, year
ORDER BY station_id, year;
Edit and Run Code