Adobe SQL interview questions – Leetcode 197 – Rising Temperature

Problem Description –

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:

Difficulty Level – Easy

Solution –

SELECT
    w1.id
FROM weather w1 JOIN weather w2
ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
AND w1.Temperature > w2.Temperature

In the question we are asked to find all dates id with higher temperature compared to to its previous dates(yesterday). To solve this problem we used a self-join of the weather table and used the condition DATEDIFF(w1.recordDate, w2.recordDate) = 1 to match the current date with the previous date and used the condition w1.Temperature > w2.Temperature to select only rows where the temperature of the day is higher than the previous day.