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.
Top comments (0)