Retiago Drago

Posted on

# Rising Temperature | LeetCode | MSSQL

## 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.

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

### 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.

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

## 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.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

## ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai