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
```