Does anyone have the solution for Q5 at the link above? I’m not sure (at all) how to handle nulls. Thanks!
I actually don’t know how to do the subsequent questions either. I used the hints, but I need to compare my code to the solutions.
This is what I have for #5. I don’t know how to handle nulls in the LAG.
SELECT state, year, tempf, tempf-LAG(tempf, 1, ?) OVER (PARTITION BY state
ORDER BY year) AS ‘change_in_temp’
FROM state_climate
ORDER BY change_in_temp;
This is what I’ve got for #5 but I’m not certain that it’s correct.
As for the nulls in the LAG, found this from the Window Functions lesson:
LAG takes up to three arguments:
-column (required)
-offset (optional, default 1 row offset)
-default (optional, what to replace default null values with)
So I’ve put in my code:
LAG(tempc, 1, tempc) → tempc for the column needed, 1 for the 1-row offset, and tempc again so that it doesn’t return a null.
That looks good. Only difference for me was I ran one table and used ABS() and ordered by DESC to figure out where the biggest changes were.
SELECT state,
year,
tempf,
ABS(ROUND(
tempf - LAG(tempf, 1, tempf) OVER (PARTITION BY state ORDER BY year)
,2)) AS change_in_temp
FROM state_climate
ORDER BY change_in_temp DESC
LIMIT 10;