max-stays-sequence-in-hive.hql

create table temp.result
as 
select uid, dtnum
from (
    select uid, rndiff, dtnum
        , row_number() over ( partition by uid order by dtnum desc ) rk
    from (
        select uid, rndiff, count(dt) dtnum
        from (
            select uid
                , dt
                , row_number() over(partition by uid order by dt) rn
                , datediff(dt, '2018-01-01') diffdays
                ,  datediff(dt, '2018-01-01') -  row_number() over(partition by uid order by dt) as rndiff
            from (
                select uid, substr(date_, 1, 10) as dt
                from A
                where udt = '20181231'
                    and time > 10
                    and date_ between '2016-10-01' and '2018-12-31'
                group by uid, substr(date_, 1, 10)
                ) tmp
        ) result
        group by uid, rndiff
    ) midresult
) finalresult
where rk = 1

max-stays-sequences-with-startend-hive.hql

create table temp.result
as 
select uid, dtnum,   startdt, enddt 
from (
    select uid, rndiff, dtnum,  startdt , enddt 
        , row_number() over ( partition by uid order by dtnum desc ) rk
    from (
        select uid, rndiff, count(dt) dtnum
            , min(dt) as startdt , max(dt) as enddt 
        from (
        
            select uid
                , dt
                , row_number() over(partition by uid order by dt) rn
                , datediff(dt, '2016-10-01') diffdays
                ,  datediff(dt, '2016-10-01') -  row_number() over(partition by uid order by dt) as rndiff
            from (
                select uid, substr(date_, 1, 10) as dt
                from  A
                where udt = '20190319'
                    and time > 10
                    and date_ between '2016-10-01' and '2019-03-19'
                    and uid = 123
                group by uid, substr(date_, 1, 10)
                ) tmp
                
        ) result
        group by uid, rndiff
    ) midresult
) finalresult
where rk = 1