American Express Interview Question | Temperature

Question

Temperatures of a particular area were recorded in a table. Write a SQL query to find the date along with the IDs whose temperature is lower from the next day.

ID Date Temperature (c)
1001 2020-03-11 17
1002 2020-03-12 20
1003 2020-03-13 31
1004 2020-03-14 23
in progress 1
Dhruv2301 4 years 2 Answers 903 views Great Grand Master 0

Answers ( 2 )

  1. Sai
    1
    Reply

    Select t2.Date from Table t1
    INNER JOIN table t2 on
    t2.ID=t1.ID+1
    where t2.Temperature <t1.Temperature;

  2. Rakesh Mehta
    0
    Reply

    with newtab as
    (
    select *,
    case when temperature < next_temp then 1 else 0 end as category
    from(
    select * ,
    lead(tempaerature) over(order by date) as next_temp
    from employee1
    ) new
    )
    select id, date
    from newtab
    where category = 1

Leave an answer

Browse
Browse