Rising Temperature

You can write a query to find the dates’ Id with higher temperatures compared to its previous dates. Here’s the SQL code:

1
2
3
4
5

SELECT w1.id
FROM Weather w1
JOIN Weather w2
ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

This query joins the Weather table with itself, matching rows where the recordDate is one day apart. It then filters the results to only include rows where the temperature is higher on the later date.

The ON keyword is used to specify the condition that must be met for the join to occur between two rows from the two instances of the Weather table (aliased as w1 and w2).

Here’s the condition:

1

ON DATEDIFF(w1.recordDate, w2.recordDate) = 1

The DATEDIFF function calculates the difference in days between two dates. In this case, it’s finding the difference between the recordDate column in the w1 alias and the recordDate column in the w2 alias.

The condition DATEDIFF(w1.recordDate, w2.recordDate) = 1 ensures that only rows where the recordDate in w1 is one day later than the recordDate in w2 will be joined.

So essentially, the ON clause is used to pair each row in the table with the row representing the previous day, allowing the query to then compare the temperatures between consecutive days.

In the context of the query, the statement within the ON clause is used to figure out the “yesterday” for each given date in the Weather table.

By using the condition DATEDIFF(w1.recordDate, w2.recordDate) = 1, the query is looking for pairs of rows where the difference in the recordDate column is exactly one day. In this pairing, w1.recordDate represents the current day, and w2.recordDate represents the previous day, or “yesterday.”

This condition ensures that the query only considers pairs of dates that are consecutive, allowing it to identify the dates with higher temperatures compared to the previous day.