Rising Temperature - Problem

Given a Weather table, write a SQL solution to find all dates' id with higher temperatures compared to their previous dates (yesterday).

The Weather table contains information about the temperature on a certain day. Each record has a unique id and there are no different rows with the same recordDate.

Return the result table in any order.

Table Schema

Weather
Column Name Type Description
id PK int Primary key, unique identifier for each weather record
recordDate date Date of the temperature record
temperature int Temperature recorded on that date
Primary Key: id
Note: Each row represents one day's weather record with unique dates

Input & Output

Example 1 — Basic Temperature Comparison
Input 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
💡 Note:

ID 2 (2015-01-02) has temperature 25°, which is higher than the previous day's temperature of 10°. ID 4 (2015-01-04) has temperature 30°, which is higher than the previous day's temperature of 20°. ID 3 doesn't qualify because 20° < 25°.
Example 2 — No Rising Temperature
Input Table:
id recordDate temperature
1 2015-01-01 30
2 2015-01-02 25
3 2015-01-03 20
Output:
id
💡 Note:

No dates have higher temperatures than their previous dates. The temperature consistently decreases: 30° → 25° → 20°, so no records are returned.

Constraints

  • 1 ≤ Weather.id ≤ 100
  • recordDate is a valid date
  • -100 ≤ temperature ≤ 100
  • All recordDate values are unique

Visualization

Tap to expand
Rising Temperature - SQL Solution INPUT: Weather Table id recordDate temp 1 2015-01-01 10 2 2015-01-02 25 3 2015-01-03 20 4 2015-01-04 30 Temperature Timeline 10 Jan 1 25 Jan 2 20 Jan 3 30 Jan 4 ^ ^ ALGORITHM STEPS 1 Self-Join Table Join Weather w1, w2 2 Match Consecutive Days w1.date = w2.date + 1 day 3 Compare Temperatures w1.temp > w2.temp 4 Return Matching IDs SELECT w1.id SELECT w1.id FROM Weather w1, Weather w2 WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 AND w1.temp > w2.temp FINAL RESULT Days with rising temperature: Jan 2 (25) vs Jan 1 (10): 25 > 10 OK Jan 3 (20) vs Jan 2 (25): 20 < 25 X Jan 4 (30) vs Jan 3 (20): 30 > 20 OK Output Table id 2 4 2 rows found with rising temp Key Insight: Use SELF-JOIN to compare each row with its previous day. The DATEDIFF function finds consecutive dates (difference = 1 day). Filter rows where today's temperature exceeds yesterday's temperature. Time Complexity: O(n) with proper indexing on recordDate. Alternative: Use LAG() window function. TutorialsPoint - Rising Temperature | Optimal Solution: Self-Join with DATEDIFF

Related Problems

Asked in
Amazon 15 Microsoft 12 Apple 8
285.0K Views
High Frequency
~8 min Avg. Time
1.8K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen