In extreme detail, I'll walk through a solution to the problem below. It's based off this Leetcode problem however I use a different example that's more similar to the "dirty" data you'll utilize on the job.
weater table, write a SQL query to find all dates with higher temperature compared to its previous day.
You should return the following table.
Id | RecordDate | Temperature | previous_day_temperature --- | --- | --- 1 | 2018-02-01 | 33 2 | 2018-02-02 | 34 3 | 2018-02-03 | 22 4 | 2018-02-05 | 38 5 | 2018-02-06 | 39
Explanation of SQL Query Below:
Weather table, each row has a unique
Id value and should also be a unique day.
Note how the
RecordDate changes from
2018-02-04. In this table, we're unsure how the
2018-02-04 compares to the previous day, the third, because there is no value of the previous days' temperature.
In the final solution SQL query, I need two conditions:
1) Previous day's date plus one day is equal to the current day's date 2)
SELF JOIN below joins the
weather table to itself on the
Id field. So, the result is
SELECT *, LAG(temperature) OVER (ORDER BY record_date ASC) AS previous_day_temperature, LAG(record_date) OVER (ORDER BY record_date ASC) AS previous_record_date FROM frieds.weather weather
SELECT weather.Id AS "Id" FROM weather JOIN weather w ON DATEDIFF(weather.RecordDate, w.RecordDate) = 1 AND weather.Temperature > w.Temperature