select user_id,dt,row_number() over (partition by user_id ,rt order by active_dt ) as result from ( select user_id,dt,row_number() over (partition by user_id order by active_dt ) as rnt, cast(active_dt as int)-row_number() over (partition by user_id order by active_dt ) as rt from table1 ) a