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.
Here's an example for better understanding:
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).
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.
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.
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
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.
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.