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 0
Dhruv2301 3 months 2 Answers 146 views Great Grand Master 0

Answers ( 2 )

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

  2. 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