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!

#4

My solution to nr 3 was different than the one in the hint.
By using MIN() I got the lowest temp per state - one row per state.

SELECT *, MIN(tempc)
FROM state_climate
GROUP BY state
ORDER BY state

WIth the FIRST_VALUE I got the lowest temp, but also all other years for each state which meant like 120 rows per state.
SELECT state, year, tempc,
FIRST_VALUE(tempc) OVER (PARTITION BY state ORDER BY tempc) AS ‘lowest_temp’
FROM state_climate
ORDER BY lowest_temp;

1 Like