Rising Temperature


  • 0

    Click here to see the full article post


  • 0
    D

    work in SQL Server
    select id
    from (select *, lag(temperature) over(order by date) as ptemp
    from Weather) a
    where Temperature > ptemp


  • 0
    S

    My work is Different:

    select now.Id as Id
    from Weather as previous,Weather as now
    where DATEDIFF(now.Date,previous.Date) = 1
    and now.Temperature > previous.Temperature


  • 1
    Z

    How about this simple way:
    select W1.Id from Weather as W1,Weather as W2
    where W1.Date-1=W2.Date and W1.Temperature>W2.Temperature;


  • 0
    B

    DATEDIFF(weather.date, w.date) = 1
    What if the date you checked is the next day, not yesterday? Will datediff return negative value?


  • 0
    G

    Write your MySQL query statement below

    select w2.Id
    from Weather w1, Weather w2
    where w2.Temperature > w1.Temperature and DATEDIFF(w2.Date, w1.Date) = 1
    order by 1;


  • 0
    N

    @Zhangdajun said in Rising Temperature:

    How about this simple way:
    select W1.Id from Weather as W1,Weather as W2
    where W1.Date-1=W2.Date and W1.Temperature>W2.Temperature;

    It can't be submitted successfully! So what's difference between "where t1.date – 1 = t2.date" and "DATEDIFF(t1.date,t2.tate)=1" ?


  • 0
    L

    Bingo

    select w1.id from
    Weather w1,Weather w2
    where
    DateDiff(w1.Date,w2.Date)=1
    and
    w1.Temperature>w2.Temperature;


  • 0
    D

    I don't know how to quote here @neverlate-leetcode...the problem with +1 or -1 is the dates get ruined. In this case 1900-01-01 then -1 becomes 1900-01-00...+1 on 31st becomes 32nd too. You can use @Zhangdajun's idea by using DATE_ADD:
    select a.id Id
    from Weather a
    inner join Weather b on b.date=date_add(a.date,interval -1 day)
    and a.temperature > b.temperature


  • 0
    F

    Same Question! what's difference between "where t1.date – 1 = t2.date" and "DATEDIFF(t1.date,t2.tate)=1" ?


  • 0
    D

    @FreyaJcY said in Rising Temperature:

    Same Question! what's difference between "where t1.date – 1 = t2.date" and "DATEDIFF(t1.date,t2.tate)=1" ?

    I explained higher up. +/- has no logic checking. 1st - 1 = 0th of the month instead of 31st of the previous


  • 0
    F

    @Dird Thanks! Clear now : )


  • 0
    L

    I need help with my answer.
    select b.Id as Id from Weather as a left join Weather as b on a.Id = b.Id - 1 where b.Temperature > a.Temperature;
    Why the above query is wrong. If I select * instead of "b.Id as Id", the answer seems right. ...


  • 0
    D
    This post is deleted!

  • 0
    C

    @liren.sun The "id" is not always directly proportional to "date".


  • 0
    R

    I didn't get why this is wrong, it worked well on 13 out of 14 test cases, is it becasue date calculation must be operated using datediff?
    select distinct w2.Id as Id
    from Weather as w2
    ,Weather as w1
    where w1.date=w2.date-1 and w1.temperature<w2.temperature


  • 0

    @rongxin_whu

    The reason is that

    w1.date=w2.date-1
    

    doesn't handle the corner case, such as w2.date = 1998-11-01


  • 0
    P

    Why is not accepted since it's running correct on W3schools?
    select A.ID
    frome Weather A, Weather B
    where A.Temperature>B.Temperature and A.ID=B.ID+1;


  • 0
    L

    select a.Id
    from Weather a, Weather b
    where a.Temperature > b.Temperature
    and DATEDIFF(b.Date, a.Date) = 1;
    I need help, why my answer is wrong? tks


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.