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.
Este ejercicio forma parte del curso
Time Series Analysis in PostgreSQL
Instrucciones del ejercicio
- Find the three highest monthly temperatures for each
station_idand correspondingstate, for the year of 2017.
Ejercicio interactivo práctico
Prueba este ejercicio y completa el código de muestra.
-- 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, ___;