Mckinsey Interview Questions | Highest Temperature
Question
You are given a table with the temperature of a particular day. Write a SQL query to find the date IDs that had higher temperature than the previous day.
ID | Date | Temperature (c) |
1 | 2020-01-01 | 17 |
2 | 2020-01-02 | 23 |
3 | 2020-01-03 | 20 |
4 | 2020-01-04 | 31 |
in progress
0
Statistics
4 years
3 Answers
965 views
Great Grand Master 0
Answers ( 3 )
SELECT
w1.id
FROM
weather w1
JOIN
weather w2 ON DATEDIFF(w1.date, w2.date) = 1
AND w1.Temperature > w2.Temperature
;
select t1.date
from table t1
self join table t2 on
datediff(t1.date, t2.date)=1
and t1.temperature > t2.temperature
select id from (
select id,date_1,temperature,lag(temperature,1) over (order by id) as temp from temperature)t where (t.temperature-t.temp)>0;