select '2020-07-20 15:00:00.000' ,1,'action-throw',' ' union all

select '2020-07-20 15:01:00.000' ,1,'result ','good' union all

select '2020-07-20 15:02:00.000' ,1,'action-push ',' ' union all

select '2020-07-20 15:03:00.000' ,2,'action-run ',' ' union all

select '2020-07-20 15:04:00.000' ,2,'result ','bad' union all

select '2020-07-20 15:05:00.000' ,2,'action-throw',' ' union all

select '2020-07-20 15:06:00.000' ,1,'action-push ',' ' union all

select '2020-07-20 15:07:00.000' ,1,'result ','bad' union all

select '2020-07-20 15:08:00.000' ,1,'action-push ',' ' union all

select '2020-07-20 15:09:00.000' ,2,'result ','good' union all

select '2020-07-20 15:10:00.000' ,2,'action-pull ',' ' union all

select '2020-07-20 15:11:00.000' ,2,'action-push ',' '

as (

select *

,case when (lag(outcome) over(partition by user1 order by event_timestamp) <> ' '

and outcome =' '

)/*Check if there has been a change in outcome by user*/

OR (outcome=' '

and row_number() over(partition by user1 order by event_timestamp)=1

)/*for the first record per user, we consider it to be a new group*/

then

row_number() over(partition by user1 order by event_timestamp)

else ' '

end as grp_val

from t

)

,computed_grp_val

as(select *

,sum(case when grp_val =' ' then 0 else 1 end) over(partition by user1 order by event_timestamp) as grp_val_1

from data

)

select *

,case when max(outcome) over(partition by user1,grp_val_1)=' ' then '?'

else max(outcome) over(partition by user1,grp_val_1)

end as comptued_outcome

,max(event_timestamp) over(partition by user1,grp_val_1) as computed_event_timestamp

from computed_grp_val

order by 1