Temperature is higher than the previous day’s

Problem Statement

You are given a table named Weather with the following structure:

CREATE TABLE Weather (
  id NUMBER PRIMARY KEY,
  record_date DATE,
  temperature NUMBER
);

You are asked to write a query that returns the IDs of the days where the temperature is higher than the previous day’s temperature.

Sample Data

INSERT INTO Weather VALUES (1, DATE '2023-07-01', 30);
INSERT INTO Weather VALUES (2, DATE '2023-07-02', 35);
INSERT INTO Weather VALUES (3, DATE '2023-07-03', 34);
INSERT INTO Weather VALUES (4, DATE '2023-07-04', 36);
INSERT INTO Weather VALUES (5, DATE '2023-07-06', 38); -- Skip July 5th
COMMIT;

Expected Output

We want a list of all IDs where the temperature is greater than the previous calendar day’s temperature.

From the data:

On July 2, 35 > 30 → include ID 2
On July 3, 34 < 35 → skip
On July 4, 36 > 34 → include ID 4
July 6 has no July 5 entry → skip

So the result is:

+----+
| id |
+----+
|  2 |
|  4 |
+----+

SQL Query Using Self Join

SELECT W1.id
FROM Weather W1
JOIN Weather W2
  ON W1.record_date = W2.record_date + 1
WHERE W1.temperature > W2.temperature;

Explanation

We join the table to itself:
- W1 = today
- W2 = yesterday
The condition W1.record_date = W2.record_date + 1 matches today’s row with the previous day’s row.
Then we check if W1.temperature > W2.temperature.
This ensures we compare only consecutive dates.

Output of the Above Query

id
2
4

Notes

This works only when there is a row for the previous date.
If there’s no row for “yesterday” (like July 5 in the example), that date will be skipped.
record_date + 1 works in Oracle because dates are numeric types where + 1 means one day ahead.

Bonus: Alternate Way Using `LAG()` (Oracle 12c+)

SELECT id
FROM (
  SELECT id, temperature,
         LAG(temperature) OVER (ORDER BY record_date) AS prev_temp
  FROM Weather
)
WHERE temperature > prev_temp;

This method uses the analytic function LAG() to access the previous row’s value.
It may not ensure consecutive calendar days, so use self-join if strict day-to-day comparison is needed.