Climate Change #5

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

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.


Need help with the code , thanks in advance.

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;