CODEWITHSUNDEEP
sqlteradata
Tejas
Tejas

Reputation: 127

Set numeric flag/rank for repetitive values in a column

I am working on piece of tracking customer journey on a application. Trying to set a rank/counter to segregate a session among multiple sessions sharing same id.(Ideally should have been unique).

current structure of table:

enter image description here

desired output:

enter image description here

Have tried using row_number over sequence number, but when a sequence_number appears for first time( 4 in case of last row), it takes to be the first count.

How should I go about splitting the data and having a session column whenever the sequence number starts at 1( indicating new session).

Upvotes: 0

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You seem to want a cumulative count of 'START's:

select t.*,
       sum(case when activity = 'START' then 1 else 0 end) over
           (partition by session_id
            order by time_stamp
            rows between unbounded preceding and current row
           ) as session
from t;

Upvotes: 1

Related Questions