hive - I am trying to write a query but it is not working with lag function

Question

I need to do partition on aa bb cc columns order by date and need results like if the difference is more than 60 days in consecutive rows. I tried with datediff(date,lag()) but not getting expected results.

question from:https://stackoverflow.com/questions/65915825/i-am-trying-to-write-a-query-but-it-is-not-working-with-lag-function

Answer 1

Assuming date is a datetime type column, you can use below code -
CASE when datediff(date,lag(Date, 1, 0) over (order by Date) ) >60 then 1 else 0 END diff_gt_60