197. Rising Temperature - Detailed Explanation

Problem Statement

You are given a table named Weather with the following columns:

• Id (int): A unique identifier for each record.
• RecordDate (date): The date of the record.
• Temperature (int): The temperature on that date.

Your task is to write a SQL query to output all records (i.e., return their Id and Temperature) where the temperature is higher than the temperature on the previous day (the record immediately preceding it by date).

Example

Consider the following sample data in the Weather table:

Output:

Explanation:

• For record with Id 2: The temperature (25) is greater than the previous day’s temperature (10 from 2015-01-01).
• For record with Id 4: The temperature (30) is greater than the previous day’s temperature (20 from 2015-01-03).

Constraints

• The table contains at least two records.
• The records have unique dates and are stored with valid dates.

Hints

1. Self-Join or Window Function:
   Think about how you can compare a record’s temperature with the previous day’s temperature. One common method is to join the table with itself where the joining condition matches a record to the record from the previous day. Another modern approach is to use a window function like LAG.

2. Date Comparison:
   Since you are comparing consecutive days, ensure that you correctly compute the difference between dates. Most SQL dialects provide functions such as DATEDIFF that can help with this.

Approach Overview

There are two common ways to solve this problem:

1. Self-Join Approach

• Idea:
  Use a self-join to pair each record with the record from the previous day. In the join condition, check that the date difference is exactly 1 day. Then, in the WHERE clause, select only those records where the current day’s temperature is higher than that of the previous day.

• Steps:

  1. Alias the Weather table as w1 for the current record.
  2. Alias the same table as w2 for the previous day’s record.
  3. Use a date function (like DATEDIFF) to join the records where the difference in RecordDate is 1.
  4. In the WHERE clause, filter out records where w1.Temperature is greater than w2.Temperature.

• SQL Example:

  SELECT
      w1.Id,
      w1.Temperature
  FROM
      Weather w1
  JOIN
      Weather w2
      ON DATEDIFF(w1.RecordDate, w2.RecordDate) = 1
  WHERE
      w1.Temperature > w2.Temperature;

2. Window Function (LAG) Approach

• Idea:
  Use the LAG window function to access the previous day’s temperature for each record. This method avoids an explicit self-join.

• Steps:

  1. Use LAG(Temperature) OVER (ORDER BY RecordDate) to retrieve the temperature of the previous day.
  2. In an outer query, filter the records where the current day’s temperature is greater than the value returned by LAG.

• SQL Example:

  SELECT Id, Temperature
  FROM (
      SELECT
          Id,
          Temperature,
          LAG(Temperature) OVER (ORDER BY RecordDate) AS PrevTemperature
      FROM Weather
  ) AS t
  WHERE Temperature > PrevTemperature;

Code Implementation

Below are the two SQL solutions:

Self-Join Solution

SELECT
    w1.Id,
    w1.Temperature
FROM
    Weather w1
JOIN
    Weather w2
    ON DATEDIFF(w1.RecordDate, w2.RecordDate) = 1
WHERE
    w1.Temperature > w2.Temperature;

Window Function (LAG) Solution

SELECT Id, Temperature
FROM (
    SELECT
        Id,
        Temperature,
        LAG(Temperature) OVER (ORDER BY RecordDate) AS PrevTemperature
    FROM Weather
) AS t
WHERE Temperature > PrevTemperature;