What is the time complexity of the Self Join approach?

O(N) assuming an index on recordDate, otherwise O(N²).

What is the time complexity of the Window Function approach?

O(N log N) due to the sorting required by the OVER clause.

Rising Temperature

Difficulty: Easy | Acceptance: 51.30% | Paid: No Topics: Database

Table: Weather

+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id is the primary key for this table. This table contains information about the temperature in a certain day.

Write an SQL query to find all dates’ Ids with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example.

Examples
Constraints
Self Join
Window Functions

Examples

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

Constraints

recordDate is unique.

Self Join

Intuition We can join the Weather table with itself. We match rows where the date of the first row is exactly one day after the date of the second row, and the temperature of the first row is higher.

Steps

Select the id from the Weather table (aliased as w1).
Join Weather with itself (aliased as w2).
Filter for records where w1.recordDate is the next day of w2.recordDate.
Filter for records where w1.temperature is greater than w2.temperature.

sql

SELECT w1.id
FROM Weather w1
JOIN Weather w2 ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
WHERE w1.temperature &gt; w2.temperature

Complexity

Time: O(N) assuming an index on recordDate, otherwise O(N²) for the join.
Space: O(1) (excluding output storage).
Notes: The Self Join approach is very standard for comparing rows in the same table based on a relationship (like date difference).

Window Functions

Intuition We can use the LAG() window function to access the temperature of the previous row (ordered by date) in the current row. Then we simply compare the current temperature with the previous temperature.

Steps

Select id, temperature, and the previous temperature (LAG) from the Weather table, ordered by recordDate.
Wrap this in a subquery or CTE.
Filter the results where the current temperature is greater than the previous temperature.

sql

SELECT id
FROM (
    SELECT id, temperature,
    LAG(temperature) OVER (ORDER BY recordDate) as prev_temp
    FROM Weather
) as temp_table
WHERE temperature &gt; prev_temp

Complexity

Time: O(N log N) due to the sorting required by the OVER (ORDER BY recordDate) clause.
Space: O(N) to store the window function results.
Notes: Window functions are often more readable but can be slightly less performant than indexed joins on very large datasets due to the sorting overhead.