Temperatures by century
As a data analyst you may be asked to perform ad hoc queries.
You work at a news organization and are in charge of providing data for the daily weather quiz that is posed in the nightly newscast. The meteorologist occasionally asks for your help to create a new weather trivia quiz. This time you are asked to provide the maximum and minimum recorded temperature for each station over each century.
You are given a sample dataset that only provides data for years 2000 through present day, but it should work on a more complete dataset containing data based in any century.
The table temperature_stations provides metadata for each weather station, and temperatures_monthly contains the monthly minimum, maximum, and average temperatures for each month.
This exercise is part of the course
Time Series Analysis in PostgreSQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Get the min and max temp for each station per century
SELECT
ts.state,
___ AS century,
___ AS min,
___ AS max
FROM temperature_stations AS ts
JOIN temperatures_monthly USING(station_id)
GROUP BY state, century
ORDER BY state;