On step 4 we are asked to retrieve the highest temperature for each state.
For the project above, I’m having a hard time understand LAST_VALUE and FIRST_VALUE, and how LAST_VALUE needs a specified range:
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
all I’m getting is a value is a single value over for every state. Here’s the request command:
SELECT state, year, tempc, FIRST_VALUE(tempc) OVER (PARTITION BY state) as 'lowest_temp', LAST_VALUE(tempc) OVER (PARTITION BY state RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as 'highest_temp' from state_climate;