SQL Joins Problem Solution

Rising Temperature

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.

Problem Statement

Given a weater table, write a SQL query to find all dates with higher temperature compared to its previous day.

Id RecordDate 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

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

Solution

Self Join Weather Table

Explanation of SQL Query Below:

In the Weather table, each row has a unique Id value and should also be a unique day.

Id RecordDate 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

Note how the RecordDate changes from 2018-02-02 to 2018-02-04. In this table, we're unsure how the Temperature on 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)

The SELF JOIN below joins the weather table to itself on the Id field. So, the result is

SQL Query:

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

Returned Output:

Id RecordDate Temperature Id RecordDate Temperature
1 2015-01-01 10 1 2015-01-01 10
2 2015-01-02 25 2 2015-01-02 25
3 2015-01-03 20 3 2015-01-03 20
4 2015-01-04 30 4 2015-01-04 30
SELECT weather.Id AS "Id"
FROM weather
JOIN weather w
ON DATEDIFF(weather.RecordDate, w.RecordDate) = 1
AND weather.Temperature > w.Temperature