Rising Temperature | LeetCode | MSSQL

The Problem

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

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

Explanation

Here's an example for better understanding:

Input:

Weather table:

Output:

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