197. Rising Temperature

Problem Statement

Table: Weather

Column Name	Type
id	int
recordDate	date
temperature	int

id is the column with unique values for this table.

There are no different rows with the same recordDate.

This table contains information about the temperature on a certain day.

Instructions

• Write a solution to find all dates' Id with higher temperatures compared to its previous dates (yesterday).
• Return the result table in any order.
• The result format is in the following example.

Example

Input: 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:

In 2015-01-02, the temperature was higher than the previous day (10 -> 25).

In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

Submissions

PostgreSQL

SELECT w1.id
FROM Weather w1
JOIN Weather w2 ON w1.recordDate = w2.recordDate + INTERVAL '1 day'
WHERE w1.temperature > w2.temperature;

Explanations

PostgreSQL

Submitted by @noeyislearning

• SELECT w1.id: Select the id from the Weather table.
• FROM Weather w1: Select the Weather table and alias it as w1.
• JOIN Weather w2 ON w1.recordDate = w2.recordDate + INTERVAL '1 day': Join the Weather table with itself on the condition that the recordDate of w1 is equal to the recordDate of w2 plus one day.
• WHERE w1.temperature > w2.temperature: Filter the rows where the temperature of w1 is greater than the temperature of w2.