Skip to content

LeetCode 197 - Rising Temperature

Database Language: MySQL

Difficulty: ⭐

Problem Description

Input

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.

Requirement

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.

Examples

Example 1

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).

SQL Schema

CREATE TABLE IF NOT EXISTS Weather (id INT PRIMARY KEY, recordDate DATE, temperature INT);
TRUNCATE TABLE Weather;
INSERT INTO Weather (id, recordDate, temperature) VALUES ('1', '2015-01-01', '10');
INSERT INTO Weather (id, recordDate, temperature) VALUES ('2', '2015-01-02', '25');
INSERT INTO Weather (id, recordDate, temperature) VALUES ('3', '2015-01-03', '20');
INSERT INTO Weather (id, recordDate, temperature) VALUES ('4', '2015-01-04', '30');

Solution

Given that there is only 1 table provided and that the question requires to compare 2 rows in the same table, this means that a SELF JOIN will be involved. A SELF JOIN is a type of join where a table is joined with itself. To join a table with itself, an alias to the table needs to be used to differentiate it when the table is being used to identify the temperature yesterday (Weather AS yesterday) and when the table is being used to identify the temperature today (Weather AS today).

Since we are only interested on the temperature of 2 consecutive days, an INNER JOIN will be used between the weather yesterday and the weather today joining on the recordDate wherein 1 day is subtracted to recordDate today and compared against the recordDate yesterday:

FROM Weather AS yesterday INNER JOIN Weather AS today
  ON DATE_SUB(today.recordDate, INTERVAL 1 DAY) = yesterday.recordDate

The DATE_SUB date/time function subtracta a time value (interval) from a date. In the query above, 1 day is being subtracted from today.recordDate to match with yesterday.recordDate.

Since we are only interested on today's temperature that is higher than yesterday's temperature, a condition will be added to the query to address this requirement:

yesterday.temperature < today.temperature

This condition can either be added as part of the INNER JOIN condition or in the WHERE clause:

Part of the INNER JOIN:

FROM Weather AS yesterday INNER JOIN Weather AS today
  ON DATE_SUB(today.recordDate, INTERVAL 1 DAY) = yesterday.recordDate AND
     yesterday.temperature < today.temperature

Part of the WHERE clause:

FROM Weather AS yesterday INNER JOIN Weather AS today
  ON DATE_SUB(today.recordDate, INTERVAL 1 DAY) = yesterday.recordDate
WHERE yesterday.temperature < today.temperature

Lastly, the requirement wants to return just the id for all rows with higher temperatures compared to yesterday's temperature, so the id of today will be returned instead of the id of yesterday:

SELECT today.id

Putting this all together yields the following queries:

# Final Solution Query
SELECT today.id
FROM Weather AS yesterday INNER JOIN Weather AS today
  ON DATE_SUB(today.recordDate, INTERVAL 1 DAY) = yesterday.recordDate AND
     yesterday.temperature < today.temperature

or

# Alternate Solution Query
SELECT today.id
FROM Weather AS yesterday INNER JOIN Weather AS today
  ON DATE_SUB(today.recordDate, INTERVAL 1 DAY) = yesterday.recordDate
WHERE yesterday.temperature < today.temperature

The query plan generated by both of these queries are the same suggesting that these 2 queries are going to be executed the same way internally:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE yesterday ALL 4 100.00
1 SIMPLE today ALL 4 33.33 Using where; Using join buffer (hash join)

Here's the faster runtime for this query:

  • Runtime: 308ms
  • Beats: 97.43% as of July 15, 2024