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
Dhruv2301 4 years 3 Answers 846 views Great Grand Master 0

Answers ( 3 )

  1. swap007
    0
    Reply

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

  2. Ognish
    0
    Reply

    select t1.date
    from table t1
    self join table t2 on
    datediff(t1.date, t2.date)=1
    and t1.temperature > t2.temperature

    1
    Reply

    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;

Leave an answer

Browse
Browse