insert into #t (id,event_time_text) values

(123456789,'070007'),

(123456789,'070048'),

(123456789,'162455'),

(123456789,'162542'),

(123456789,'184131'),

(123456789,'184207');

update #t set event_time = TIMEFROMPARTS(LEFT(event_time_text,2),SUBSTRING(event_time_text,3,2),SUBSTRING(event_time_text,5,2),0,0);

WITH dat --calculate the difference in minutes of one row from the previous row

(

SELECT *

,CASE

WHEN DATEDIFF(minute,LAG(event_time,1,event_time) OVER (PARTITION BY id ORDER BY event_time),event_time) <= 2

THEN LAG(event_time,1,event_time) OVER (PARTITION BY id ORDER BY event_time)

ELSE event_time

END AS diff_index

FROM #t

)

SELECT *

,DENSE_RANK() OVER (PARTITION BY id ORDER BY diff_index) AS group_within_id

FROM dat;