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.
Deze oefening maakt deel uit van de cursus
Time Series Analysis in PostgreSQL
Oefeninstructies
- Find the three highest monthly temperatures for each
station_idand correspondingstate, for the year of 2017.
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
-- 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, ___;