[Leetcode-SQL50] 197. Rising Temperature

[Leetcode-SQL50] 197. Rising Temperature

[NOTICE] Please note that this is based on ‘leetcode’ and is posted for personal learning purposes.

1.Question

Write a solution to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

2.Key concept

• MySQL: DATEDIFF(), DATE_ADD()
• Postgres: INTERVAL

2.1 DATEDIFF()

<MySQL>

SELECT w1.id

FROM Weather AS w1 , Weather AS w2

WHERE w1.Temperature > w2.Temperature AND DATEDIFF(w1.recordDate , w2.recordDate) = 1

2.2 DATE_ADD()

<MySQL>
SELECT
  t1.id
FROM
  Weather t1
  JOIN Weather t2 ON t1.recordDate = DATE_ADD(t2.recordDate, INTERVAL 1 DAY)
WHERE
  t1.temperature > t2.temperature;

2.3 INTERVAL

<PostgreSQL>

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