Table: Weather
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id is the column with unique values for this table. There are no different rows with the same recordDate. This table contains information about the temperature on a certain day.
Write a solution to find all dates' id with higher temperatures compared to its previous dates (yesterday).
Return the result table in any order.
The result format is in the following example.
Example 1:
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 | +----+ Explanation: In 2015-01-02, the temperature was higher than the previous day (10 -> 25). In 2015-01-04, the temperature was higher than the previous day (20 -> 30).
The key idea in Rising Temperature is to identify days where the temperature is higher than the previous day. Since the data is stored in a database table, the problem is best solved using SQL techniques that allow row comparison across different dates.
A common strategy is to compare each record with the previous day's record. This can be done using a self join on the table where the date difference between two rows is exactly one day. Once the rows are aligned, you simply check whether the current day's temperature is greater than the previous day's temperature.
Another modern approach uses SQL window functions, such as LAG(), to access the temperature of the previous row directly without a join. This method is often cleaner and easier to read when supported by the database engine.
Both approaches rely on scanning the dataset and comparing adjacent days, making them efficient for typical interview scenarios.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| Self Join on Date Difference | O(n) | O(1) |
| Window Function (LAG) | O(n) | O(1) |
NeetCode
This approach uses SQL's self-join capabilities. We join the Weather table with itself by matching the current day's date with the day after the previous date, effectively allowing us to compare temperatures between consecutive days.
This way, we filter for cases where the current day's temperature is higher than the previous day's temperature.
Time Complexity: O(n), where n is the number of records, as we are checking each record with its previous day's record.
Space Complexity: O(1), as no extra space is used except for the result set.
1SELECT w1.id FROM Weather w1 JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1 WHERE w1.temperature > w2.temperature;The SQL code joins the Weather table with itself. For each record in the first instance (w1), it compares with records in the second instance (w2) where the difference in days (DATEDIFF) is exactly one, meaning consecutive days. Then, it filters those records where the temperature of w1 is greater than w2.
This approach utilizes SQL window functions, specifically the LAG function, which allows you to access the value of a column from the previous row.
We use LAG to find the previous day's temperature for each record and then filter the results where the current day's temperature is higher than the previous day's temperature.
Time Complexity: O(n), due to scanning through all rows once with window function.
Space Complexity: O(n), due to the subquery which temporarily holds additional columns.
1SELECT id FROM (SELECT id, temperature, LAG(temperature, 1) OVER (This approach involves performing a self-join on the Weather table. We join the table with itself where the recordDate of the second table (alias W2) is one day earlier than the recordDate of the first table (alias W1). This allows us to compare the temperatures of each date with its previous day.
Time Complexity: O(n log n) due to the use of joins, assuming a general sort operation for join implementation. Space Complexity: O(n) for storing join results.
1SELECT W1.id FROM Weather W1 JOIN Weather W2 ON W1.recordDate = DATE_ADD(W2.recordDate, INTERVAL This approach leverages window functions to compare each day's temperature with the previous day's temperature without explicitly joining the table. By using the LAG function, we can access the previous record's temperature for comparison.
Time Complexity: O(n) as window functions efficiently traverse the data in one pass. Space Complexity: O(n) to store intermediate results of window functions.
1SELECT id FROM (SELECT id, temperature, LAG(temperature) OVER (ORDER BY recordDate)Watch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
Yes, database comparison problems like Rising Temperature are commonly used in technical interviews. They test your ability to manipulate relational data, perform joins, and use SQL analytical functions.
The optimal approach compares each day's temperature with the previous day using SQL. This can be done with a self join on the table using a one‑day date difference or by using a window function like LAG() to access the previous row's temperature.
Yes. Many SQL databases support window functions such as LAG(), which can retrieve the previous row's value within a partition. This allows you to compare temperatures without performing a self join.
In SQL problems like this, the most useful features are self joins and window functions. Window functions such as LAG() are often preferred because they allow direct access to the previous row without writing complex joins.
The SQL code first creates a subquery with the Weather data, adding a new column prev_temp using the LAG window function. LAG(temperature, 1) gives the temperature of the previous day. Then, the outer query selects rows where the current day's temperature is greater than prev_temp.
This SQL solution utilizes a self-join to compare the temperature of each day with its previous day. We join the Weather table with itself using the condition that W1's recordDate should be exactly one day after W2's recordDate. Then, we select the IDs where W1's temperature is greater than W2's temperature.
This solution employs the LAG window function to access the temperature of the previous day for each record. We create a subquery that includes a column for the previous day's temperature calculated by LAG. Finally, we filter where the current temperature is greater than the previous day's temperature.