Friday, January 22, 2021
More

    Rising Temperature

    Must Read

    Programmerhttp://www.improgrammer.net
    We started this site to inspire young minds to motivate and encourage them towards Programming Language. In this site you will get programming tutorials, tech, programming facts, programming fun and programming blogs.

    Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

    +---------+------------+------------------+
    | Id(INT) | Date(DATE) | Temperature(INT) |
    +---------+------------+------------------+
    |       1 | 2015-01-01 |               10 |
    |       2 | 2015-01-02 |               25 |
    |       3 | 2015-01-03 |               20 |
    |       4 | 2015-01-04 |               30 |
    +---------+------------+------------------+
    

    For example, return the following Ids for the above Weather table:

    +----+
    | Id |
    +----+
    |  2 |
    |  4 |
    +----+

    solution: 
    Approach 1:
    SELECT w1.Id from
    Weather w1 Inner Join Weather w2
    where w1.Date = DATE_ADD(w2.Date, INTERVAL 1 DAY) && w1.Temperature > w2.Temperature

    Approach 2:

    SELECT w2.Id FROM Weather w1 JOIN Weather w2 ON TO_DAYS(w1.Date) + 1 = TO_DAYS(w2.Date)
    WHERE w2.Temperature > w1.Temperature;
    

    Optimized Approach 3:

    select Id from(
    select case
    when Temperature>@pretemp and DATEDIFF(Date,@predate)=1 then Id else null end as Id,
    @pretemp:=Temperature,
    @predate:=Date
    from Weather, (select @pretemp:=null, @predate:=0) init
    order by Date 
    ) w
    where w.Id is not null
    order by Id asc
    
    

     

    Latest Articles

    More Recipes Like This