CODEWITHSUNDEEP
sqlhiveduplicatesflagsfind-occurrences
Mixer
Mixer

Reputation: 183

Hive SQL: How to find and flag occurrence within date range

I have a set of data below and I need to find and flag who has called back within 5 days based on phone number and call date as below. How do I do this in hive? Thank you for your help. 

PhoneNumber CallDate    Callback_Flag
    5713555841  5/6/2016    Yes
    5713555841  5/9/2016    No
    5713555841  5/19/2016   No
    5714390412  1/15/2016   Yes
    5714390412  1/19/2016   No
    5714390412  2/16/2016   No
    5714390412  3/24/2016   No
    5756379433  3/11/2016   Yes
    5756379433  3/16/2016   No
    5756379433  4/12/2016   No

Upvotes: 1

Views: 459

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Use lead():

select t.*,
       (case when lead(calldate) over (partition by phonenumber order by calldate) > date_add(CallDate, 5)
             then 'Yes'
             else 'No'
        end)
from t;

Upvotes: 1

Related Questions