ADVANCED SQL Climate Change

Get Help SQL
#1

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/advanced-sql/modules/sql-window-functions/projects/sql-window-functions-project

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;

#2

My interpretation of Step 4 was that instead of doing the lowest_temp, that you should just do the highest_temp. Not sure if including it would prevent the test from passing or not. I know it passes with just the highest_temp.

Aside from that (which may not matter), I think you’re missing an ORDER BY in your window function so that the last value that you grab is the highest

2 Likes
#3

Aha! Thanks @selectall! Why is that?? I’d have to say that it seems like an weird feature!