Rising Temperature | LeetCode | MSSQL
Tags: leetcode, beginners, codenewbie, mssql
Published: 2023-05-24 17:02:00

## The Problem

The problem pertains to the `Weather` table, which is structured as follows:

| id (PK) | recordDate | temperature |
|---------|------------|-------------|
| int     | date       | int         |

This table includes the temperature for a specific date. The objective is to write an SQL query to identify the `id`s for the dates with a temperature higher than the previous date's temperature.

## Explanation

Here's an example for better understanding:

Input: 

`Weather` table:

| id | recordDate | temperature |
|----|------------|-------------|
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |

Output:

| id |
|----|
| 2  |
| 4  |

On 2015-01-02, the temperature was higher than the previous day's (10 -> 25). On 2015-01-04, the temperature was also higher than the previous day's (20 -> 30).

## The Solution

We'll dive into two SQL solutions that approach this problem from different angles. We'll go over the key differences, strengths, weaknesses, and structures of each.

### Source Code 1

The first solution uses the `LAG()` function to access data of the previous row (day in our case). It then compares the current temperature to the previous one and also ensures that the previous day was indeed the day before the current record.

```sql
WITH rising_temp AS (
    SELECT
        *,
        LAG(temperature) OVER (ORDER BY recordDate) [prev_temp],
        LAG(recordDate) OVER (ORDER BY recordDate) [prev_date]
    FROM Weather
)
SELECT id
FROM rising_temp
WHERE
    temperature > prev_temp
    AND
    DATEDIFF(DAY, prev_date, recordDate) = 1
```

This solution takes 800ms to execute, outperforming 55.40% of other submissions.

![solution1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zmpe05wdyzhzwqisut7p.png)

### Source Code 2

The second solution resembles the first one, but it extracts the difference in days between the current and previous record in the CTE, simplifying the final `SELECT` statement.

```sql
WITH rising_temp AS (
    SELECT
        id,
        temperature,
        LAG(temperature) OVER (ORDER BY recordDate) as prev_temp,
        DATEDIFF(DAY, LAG(recordDate) OVER (ORDER BY recordDate), recordDate) as date_diff
    FROM Weather
)
SELECT id
FROM rising_temp
WHERE
    temperature > prev_temp
    AND
    date_diff = 1
```

This solution runs in 854ms, beating 44.49% of other submissions.

![solution2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8w1w40gc1h37j633zp4t.png)

## Conclusion

Each solution successfully identifies the `id`s for which the temperature was higher than on the previous day. However, their performance differs. Ranking the solutions by performance, from best to worst, we have: Source Code 1 > Source Code 2.

This ranking should guide you in choosing the most appropriate solution based on your specific performance needs.

You can find the original problem at [LeetCode](https://leetcode.com/problems/rising-temperature/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}