Rising Temperature | LeetCode 197 | Crack SQL Interviews in 50 Qs

Welcome to problem 9 of the Basic Joins series from Crack SQL Interview in 50 Questions. In this chapter, we explore how to compare rows with their chronological predecessors using self-joins and date manipulation functions in MySQL.

1. Problem Statement & Schema Overview

We are given a single database table named Weather containing temperature records across various dates. Our objective is to find all record IDs where the temperature is strictly higher than that of its previous consecutive calendar day (yesterday).

Table Schema: `Weather`

Column Name	Data Type	Description	Key / Constraint
`id`	Integer	Unique identifier for the weather reading	Primary Key
`recordDate`	Date	The calendar date of the reading	Chronological date
`temperature`	Integer	The recorded temperature on that date	Numeric value

Sample Input Dataset

`id`	`recordDate`	`temperature`
1	`2015-01-01`	10
2	`2015-01-02`	25
3	`2015-01-03`	20
4	`2015-01-04`	30

Expected Output

`id`
2
4

2. Core Mental Model: The Chronological Comparison Dilemma

Why Standard Queries Fail

In SQL, tables are inherently unordered sets of rows. When you look at row id = 3 (2015-01-03), the database engine does not inherently know that row id = 2 came immediately before it unless explicitly instructed. We cannot simply compare row $N$ with row $N-1$ by physical table order, because rows might not be physically stored or returned in chronological order.

Real-Life Analogy: The Daily Diary Check

Imagine you are maintaining a daily weather journal. To find out if today was warmer than yesterday, you cannot just look at today's entry in a vacuum. You have to physically flip back to the previous page (yesterday's date) and compare the two numbers side-by-side.

Current Page (w1): Today's temperature.
Previous Page (w2): Yesterday's temperature (exactly 1 day prior).

3. The Self-Join Strategy

To compare rows within the same table, we must perform a Self-Join. This involves treating the Weather table as if it were two separate tables (w1 and w2) and pairing every record in w1 with every record in w2 based on specific matching criteria.

Structure & Concept Flow

Weather Table (w1) Self-Joined Weather Table (w2)

[ id, recordDate, temp ] <---> [ id, recordDate, temp ]

Understanding Join Types in This Context

Inner Join: Returns only the records that satisfy the join condition. Because we want to pair specific consecutive dates, an inner join filters out non-matching rows.
Left Join: Returns all records from the left table (w1) and the matched records from the right table (w2).

Note: Because both w1 and w2 point to the exact same dataset, using an INNER JOIN or a LEFT JOIN yields identical results here, provided our filter conditions precisely isolate the previous day.

4. Establishing the Two Critical Conditions

To solve this problem accurately, our query must validate two strict business rules simultaneously:

Consecutive Date Validation: The record date in w1 must be exactly one day after the record date in w2. Non-consecutive dates (e.g., comparing Jan 4th directly to Jan 2nd if Jan 3rd were missing) must be strictly excluded.
Temperature Threshold Validation: The temperature in w1 must be strictly greater than ( $>$ ) the temperature in w2.

5. Step-by-Step Code Evolution

Approach 1: Using `DATEDIFF()` Function

The DATEDIFF(date1, date2) function in MySQL calculates the difference in days between two dates (date1 - date2).

SQL

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

How Approach 1 Works:

JOIN Weather w2: Creates a Cartesian product of the table with itself, aliased as w1 (current day) and w2 (comparison day).
ON DATEDIFF(w1.recordDate, w2.recordDate) = 1: Ensures that w1.recordDate is exactly 1 day ahead of w2.recordDate. For example, if w1 is 2015-01-02 and w2 is 2015-01-01, DATEDIFF('2015-01-02', '2015-01-01') evaluates to 1.
WHERE w1.temperature > w2.temperature: Filters the paired rows to retain only those where the temperature rose compared to the previous day.
SELECT w1.id: Returns the ID of the current warmer day.

Approach 2: Moving Conditions to the `ON` Clause

You can also place the date and temperature filters directly inside the ON clause instead of separating them into a WHERE clause. Both methods produce the exact same execution plan and output in MySQL.

SQL

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
  AND w1.temperature > w2.temperature;

Approach 3: Using Date Arithmetic (`SUBDATE()`)

An alternative way to check for consecutive dates without using DATEDIFF is to manipulate the date using subtraction. If w2 is yesterday, then subtracting 1 day from w1's date should equal w2's date, or vice-versa.

SQL

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

Alternatively, subtracting 1 day from w2 to match w1:

SQL

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON w2.recordDate = SUBDATE(w1.recordDate, 1)
WHERE w1.temperature > w2.temperature;

6. Edge Case Analysis: Why Missing Dates Matter

Consider what happens if a date is missing in the sequence. Suppose our table contains:

`id`	`recordDate`	`temperature`
1	`2015-01-01`	10
2	`2015-01-02`	25
Note: `2015-01-03` is missing entirely, and the next record is `2015-01-04` with temperature `30`.

If we mistakenly compared rows based purely on physical table order ( $N$ vs $N-1$ ):

Row 2 (2015-01-02) would be compared against Row 1 (2015-01-01).
Row 4 (2015-01-04) would be compared against Row 2 (2015-01-02).
- Wait! 2015-01-04 minus 2015-01-02 is 2 days, not 1 day.
- Because our DATEDIFF(w1.recordDate, w2.recordDate) = 1 condition strictly enforces a 1-day gap, 2015-01-04 will not be incorrectly matched with 2015-01-02. It requires a consecutive previous day (2015-01-03), which doesn't exist, correctly preventing a false positive.

7. Performance & Interview Insights

Optimization Aspect	Description
Index Recommendation	For large datasets, ensure an index is placed on `recordDate` (`CREATE INDEX idx_recordDate ON Weather(recordDate);`) to speed up date-difference calculations and self-join lookups.
Time Complexity	$\mathcal{O}(N^2)$ in the worst-case unindexed Cartesian product without join optimization, reduced significantly to $\mathcal{O}(N \log N)$ or $\mathcal{O}(N)$ by the query optimizer when indexes are present.
Interview Tip	Interviewers love this question because it tests your ability to query tables relative to themselves (Self-Joins) rather than treating rows in isolation. Always mention date-gap awareness (consecutive checks) to impress the interviewer.

Summary and Key Takeaways

Self-Joins are Essential: When a problem requires comparing a row with preceding or succeeding rows in the same table, alias the table into two distinct instances (w1 and w2).
Date Functions Simplify Logic: Use MySQL's DATEDIFF() or DATE_ADD() / SUBDATE() functions to accurately calculate chronological intervals rather than relying on brittle row IDs.
Strict Consecutiveness: Always verify that the comparison is made against yesterday (INTERVAL 1 DAY), preventing erroneous matches across missing calendar dates.
Clause Flexibility: Filtering conditions can be placed in either the ON clause (as join predicates) or the WHERE clause, though putting structural join relationships in ON is standard best practice.
Primary Key Integrity: The id column serves as the primary key and unique identifier, ensuring that our returned output rows are distinctly addressable.