0 of 50 solved/50

197. Rising Temperature

SQL 50 · Basic Joins (day 2). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

EasySolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One day that was warmer than the day before.

2. Approach

Self join with DATEDIFF(w1.recordDate, w2.recordDate) = 1, then compare temperatures.Self join with w1.recordDate = w2.recordDate + 1 (date + integer adds days), then compare temperatures.

3. The trap

Never assume id + 1 is the next day. Dates can be out of order or have gaps.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON w1.recordDate = w2.recordDate + 1
WHERE w1.temperature > w2.temperature;

Patterns used

Which join · Phrase finder · MySQL vs PostgreSQL