Looping identifier with condition-CodePudding

I want to somehow make the identifier column exist, is it possible to have like data below? logic: everytime row of type have the same data, the identifier repeat until there is different type of data, even there are already the same type of data before

dt          type    user_id  trx  identifier
2021-08-28  online  100      001  1
2021-08-29  online  100      002  1
2021-08-30  offline 100      003  2
2021-09-04  online  100      004  3
2021-09-08  web     100      005  4
2021-09-09  online  100      006  5
2021-09-11  offline 100      007  6
2021-09-15  offline 100      008  6

CodePudding user response：

Consider below approach

select * except(new_type), 
  countif(new_type) over(partition by user_id order by trx) as identifier
from (
  select *, ifnull(type != lag(type) over(partition by user_id order by trx), true) as new_type
  from data 
)

if applied to sample data in your question - output is