SELECT user_id, active_dt, row_number() OVER(PARTITION BY active_days ORDER BY active_dt) active_days FROM ( SELECT user_id, active_dt,TO_DATE(active_dt,'YYYYMMDD') - row_number() OVER(order by active_dt) active_days FROM table1 )