Rising Temperature - Practice Coding Problems

Rising Temperature - Problem

Database Easy

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

Understanding the Visualization

Input

Weather table with daily temperatures

Self-Join

Match consecutive dates

Filter

Find temperature increases

Key Takeaway

🎯 Key Insight: Self-join is perfect for comparing records with their chronological neighbors

Asked in

A Amazon 15 M Microsoft 12 🍎 Apple 8

Use self-join to compare each date with the previous day. Join the Weather table with itself where w1.recordDate = w2.recordDate + INTERVAL '1 day' and filter for w1.temperature > w2.temperature.

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

Common Approaches

Approach	Time	Space	Notes
✓ Self-Join with Date Comparison	O(n²)	O(n)	Join the table with itself to compare temperatures of consecutive dates

Self-Join with Date Comparison — Algorithm Steps

Step 1: Join Weather table with itself using aliases
Step 2: Match records where one date is exactly one day after another
Step 3: Filter for cases where today's temperature > yesterday's temperature

Visualization

Tap to expand

Step-by-Step Walkthrough

Self-Join

Join table with itself

Date Match

Match consecutive dates

Temperature Filter

Find temperature increases

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Self-join compares each row with every other row

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary join result storage

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Self-Join with Date Comparison — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler