LeetCode 197: Rising Temperature Solution in SQL Explained

Finding days with rising temperatures might feel like tracking a warming trend in a weather log, and LeetCode 197: Rising Temperature is an easy-level challenge that makes it approachable! Given a Weather table with columns id, recordDate, and temperature, you need to write an SQL query to return the ids of days where the temperature rose compared to the previous day (i.e., consecutive dates). In this blog, we’ll solve it with SQL, exploring two solutions—Self Join with Date Difference (our best solution) and Subquery with LAG (a practical alternative). With step-by-step examples, detailed query breakdowns, and tips, you’ll master this problem. While this is an SQL challenge, you can explore Python basics at Python Basics for related coding skills. Let’s spot those warming days!

Problem Statement

In LeetCode 197, you’re given a Weather table:

id (int, primary key)
recordDate (date)
temperature (int)

Your task is to write an SQL query to:

Identify days where the temperature is higher than the previous day’s.
Ensure the days are consecutive (difference of 1 day).
Return the ids of those days, ordered arbitrarily.

This differs from deduplication like LeetCode 196: Delete Duplicate Emails, focusing on temporal comparison rather than data cleanup.

Constraints

Table has at least 2 rows.
recordDate is unique (no duplicate dates).
temperature is an integer.

Example

Let’s see a case:

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:
<ul>
<li>Jan 1 (10) → Jan 2 (25): 25 > 10, consecutive → id=2.</li>
<li>Jan 2 (25) → Jan 3 (20): 20 < 25 → no rise.</li>
<li>Jan 3 (20) → Jan 4 (30): 30 > 20, consecutive → id=4.</li>
</ul>

Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-03 | 15          |
+----+------------+-------------+

Output:
+----+
| id |
+----+
+----+
Explanation: Non-consecutive dates (Jan 1 to Jan 3), no output.

These examples show we’re finding consecutive rising temperatures.

Understanding the Problem

How do you solve LeetCode 197: Rising Temperature in SQL? We need to:

Compare each day’s temperature with the previous day’s.
Check if dates differ by exactly 1 day.
Return the id of days where temperature rises.
Handle cases with gaps or no rises.

For "id=1, 2015-01-01, 10; id=2, 2015-01-02, 25", id=2 is included (25 > 10, consecutive). We need a query to join or lag rows efficiently, not a line extraction task like LeetCode 195: Tenth Line. We’ll use: 1. Self Join with Date Difference: Clear, efficient—our best solution. 2. Subquery with LAG: Alternative approach.

Let’s dive into the best solution.

Best Solution: Self Join with Date Difference Approach

Explanation

Self Join with Date Difference finds rising temperatures by:

Joining Weather with itself (w1 and w2) on consecutive dates.
Using DATEDIFF(w1.recordDate, w2.recordDate) = 1 to ensure 1-day difference.
Filtering where w1.temperature > w2.temperature.
Selecting w1.id (the later day).

This ensures O(n²) time without indexing (O(n) with date index), O(1) space beyond query execution, and clarity by directly comparing consecutive rows.

For "id=1, 2015-01-01, 10; id=2, 2015-01-02, 25":

Join: (2, 2015-01-02, 25) with (1, 2015-01-01, 10).
Date diff = 1, 25 > 10 → id=2.

Step-by-Step Example

Example 1: Weather = {1, "2015-01-01", 10{, {2, "2015-01-02", 25{, {3, "2015-01-03", 20{, {4, "2015-01-04", 30{

Goal: Return id=2, id=4.

Step 1: Self-join Weather as w1 and w2.

w1.recordDate - w2.recordDate = 1:

(2, 2015-01-02, 25) joins (1, 2015-01-01, 10).
(3, 2015-01-03, 20) joins (2, 2015-01-02, 25).
(4, 2015-01-04, 30) joins (3, 2015-01-03, 20).

Step 2: Filter w1.temperature > w2.temperature.

(2, 25) vs (1, 10): 25 > 10 → id=2.
(3, 20) vs (2, 25): 20 < 25 → no match.
(4, 30) vs (3, 20): 30 > 20 → id=4.

Step 3: Select w1.id.

Result: 2, 4.

Finish: Return {2, 4{.

Example 2: Weather = {1, "2015-01-01", 10{, {2, "2015-01-03", 15{

Goal: Return empty.

Step 1: Join:

No consecutive pairs (Jan 1 to Jan 3 = 2 days).

Step 2: Filter:

No matches.

Step 3: Result: Empty.
Finish: Return { {.

How the Code Works (Self Join with Date Difference) – Detailed Line-by-Line Explanation

Here’s the SQL query with a thorough breakdown:

-- Line 1: Select id from w1
SELECT 
    w1.id
    -- Id of day with rising temp (e.g., 2)

-- Line 2: Join Weather with itself
FROM 
    Weather w1
    -- First instance (current day, e.g., 2015-01-02)
INNER JOIN 
    Weather w2
    -- Second instance (previous day, e.g., 2015-01-01)

-- Line 3: Match consecutive days
ON 
    DATEDIFF(w1.recordDate, w2.recordDate) = 1
    -- w1 is 1 day after w2 (e.g., Jan 2 - Jan 1 = 1)

-- Line 4: Filter rising temperature
WHERE 
    w1.temperature > w2.temperature
    -- Current temp > previous (e.g., 25 > 10)

This detailed breakdown clarifies how the self join with date difference efficiently finds rising temperature days.

Alternative: Subquery with LAG Approach

Explanation

Subquery with LAG finds rising temperatures by:

Using LAG to get the previous day’s temperature and date.
Filtering where current temp > previous temp and dates differ by 1.
Selecting id of matching rows.

It’s a practical alternative, O(n) time (single pass with window function), O(n) space for window computation, but requires modern SQL support (e.g., MySQL 8.0+), and is less intuitive than a join.

For "id=2, 2015-01-02, 25; id=1, 2015-01-01, 10":

LAG: Previous temp=10, date=2015-01-01.
25 > 10, date diff = 1 → id=2.

Step-by-Step Example (Alternative)

For the first example:

Step 1: Add LAG columns.

id=1: temp=10, prev_temp=null, prev_date=null.
id=2: temp=25, prev_temp=10, prev_date=2015-01-01.
id=3: temp=20, prev_temp=25, prev_date=2015-01-02.
id=4: temp=30, prev_temp=20, prev_date=2015-01-03.

Step 2: Filter:

id=2: 25 > 10, diff=1 → id=2.
id=3: 20 < 25 → no match.
id=4: 30 > 20, diff=1 → id=4.

Step 3: Result: 2, 4.
Finish: Same output.

How the Code Works (Subquery with LAG)

SELECT id
FROM (
    SELECT 
        id, 
        temperature,
        LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp,
        DATEDIFF(recordDate, LAG(recordDate) OVER (ORDER BY recordDate)) AS date_diff
    FROM Weather
) t
WHERE temperature > prev_temp AND date_diff = 1

Complexity

Self Join with Date Difference:

Time: O(n²) – join without index (O(n) with index).
Space: O(1) – minimal overhead.

Subquery with LAG:

Time: O(n) – window function pass.
Space: O(n) – window storage.

Efficiency Notes

Self Join with Date Difference is the best solution with O(n²) time (optimized with indexing) and O(1) space, offering clarity and broad compatibility—Subquery with LAG uses O(n) time and O(n) space, requiring window function support, making it faster but less portable.

Key Insights

Self Join: Direct comparison.
LAG: Window-based lag.
Date Diff: Ensures consecutiveness.

Additional Example

Weather:
| 1 | 2023-01-01 | 5  |
| 2 | 2023-01-02 | 10 |
Output:
| 2 |
Explanation: 10 > 5, consecutive.

Edge Cases

No Rise: Empty output.
Gaps: No matches.
Two Rows: Rise or empty.

Both solutions handle these well.

Final Thoughts

LeetCode 197: Rising Temperature in SQL is a neat temporal challenge. The Self Join with Date Difference solution excels with its simplicity and compatibility, while Subquery with LAG offers a modern alternative. Want more SQL? Try LeetCode 196: Delete Duplicate Emails for deduplication or explore Python Basics for coding skills. Ready to practice? Solve LeetCode 197 on LeetCode with the example table, aiming for rising temp ids—test your skills now!