Find the three highest values per partition
You work for a meteorology observatory and the station director has tasked you with identifying the three highest monthly temperatures for each weather station for the year of 2017. You know that in order to find this you will need to create a window function to number the rows.
Since you are looking for the highest monthly temperatures, you'll want to ensure that you are ordering by the t_monthly_max in descending order so that the highest temperatures are numbered first.
Two tables are available for you: temperatures_monthly and temperature_stations.
This exercise is part of the course
Time Series Analysis in PostgreSQL
Exercise instructions
- Find the three highest monthly temperatures for each
station_idand correspondingstate, for the year of 2017.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Three highest monthly temps per station for 2017
SELECT * FROM
( SELECT
ts.station_id,
ts.state,
tm.year_month,
tm.t_monthly_max,
___ AS rank
FROM temperatures_monthly AS tm
JOIN temperature_stations AS ts USING(station_id)
WHERE ___
) AS temp_2017
WHERE ___
ORDER BY station_id, ___;