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.

Rating: 1 out of 5.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s