SQL LeetCode: 197. Rising Temperature

Pallavi Mirajkar Dantkale
2 min readApr 3, 2021

197. Rising Temperature

Table: Weather

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

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 (20 -> 30).
  1. Using lead() function

SELECT lead_id as id
FROM (
SELECT
id,
recordDate,
Temperature,
lead(recordDate,1) OVER (ORDER BY recordDate asc) as lead_recdate,
lead(Temperature, 1) OVER (ORDER BY recordDate asc) as lead_nextday_temp,
lead(id, 1) OVER (ORDER BY recordDate asc) as lead_id
FROM
Weather ) lead_temp_table
WHERE lead_nextday_temp > Temperature
and DATEDIFF(lead_recdate, recordDate) =1

2. Using self join:

SELECT
distinct w2.id as Id
FROM Weather w1
JOIN Weather w2
ON DATEDIFF(w2.recordDate, w1.recordDate)=1
AND w2.TEmperature > w1. Temperature

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

Pallavi Mirajkar Dantkale
Pallavi Mirajkar Dantkale

Written by Pallavi Mirajkar Dantkale

QA Engineer / Data Analyst — Highly committed to Quality Assurance and data analysis, advocate for quality and add the right value to the organization.

Responses (1)

Write a response