select user_id, active_at, row_number()over(partition by t1 order by active_at) from (select user_id, active_at, date_sub(activer_at,row_number()over(partition by user_id order by active_at)) as t1, count(1) from table1 group by user_id, active_at, date_sub(activer_at,row_number()over(partition by user_id order by active_at)) )