Rising Temperature Problem


Description

LeetCode Problem 197.

Table: Weather

1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature in a certain day.

Write an SQL query to find all dates’ id with higher temperature compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
+----+
In 2015-01-02, temperature was higher than the previous day (10 -> 25).
In 2015-01-04, temperature was higher than the previous day (30 -> 20).


MySQL Solution

1
2
3
4
select w1.id
from Weather w1, Weather w2
where (w1.Temperature > w2.Temperature and 
	datediff(w1.recordDate, w2.recordDate) = 1)




Related Posts

Department Top Three Salaries Problem

LeetCode 185. Write a SQL query to find employees who...

Department Highest Salary Problem

LeetCode 184. Write a SQL query to find employees who...

Rising Temperature Problem

LeetCode 197. Write an SQL query to find all dates’...

Delete Duplicate Emails Problem

LeetCode 196. Write a SQL query to delete all duplicate...

Duplicate Emails Problem

LeetCode 182. Write a SQL query to find all duplicate...

Customers Who Never Order Problem

LeetCode 183. Write a SQL query to find all customers...