select user_id, active_dt, if(count = 3,rank,1) active_days from ( select user_id,active_dt,rank, count(*)over(partition by user_id,sub_date) count from ( select user_id,active_dt,rank, (cast(active_dt as bigint) - rank) sub_date from (select user_id,active_dt, rank() over (partition by user_id order by active_dt) rank from table1) t1 ) t2 ) t3