Query that groups rows based on the difference between datetime column value from a previous row?

I have a strage case of grouping query.

I need to write a sql server query that groups rows based on the difference between datetime column value from a previous row.

That's the query:

select
   [id],
   [timestamp],
   LAG([timestamp]) OVER (ORDER BY [id]) as [prev_timestamp],
   datediff(second, [timestamp], LAG([timestamp]) OVER (ORDER BY [id])) as [timestamp_diff]
from orders
order by Id

Which returns results something like this:

• the last column is what i'm trying to achieve
• clarification - the count column should be per group. not aggregative and independent from the other counts.

Couple of things to notice (also regarding the real situation):

1. A new group starts when the time difference between two consecutive rows is between -1 and -10 seconds. The group terminates at the last row where this condition applies.
2. Certain time differences should be ignored:
   • 0 seconds
   • Greater than 1 second
   • Less than -11 seconds
3. The count should be recorded in the same row where the group starts.
4. The id values are not sequential, as they belong to a specific user. The final query should also group results by userId.
5. In reality differences can be anything from a few seconds to several day long - in the past or in the future.

I've attempted various approaches using LEAD(), LAG(), and cursors, but due to the dynamic nature of the groups, I haven't been able to get it to work correctly.

Also i saw some questions already here on this, some on datediff, some on grouping dates, but none of them are what i'm looikng for.

This feels a little heavy handed, but I believe it captures your criteria:

with cte AS 
(
  select
   [id],
   [timestamp],
   LAG([timestamp]) OVER (ORDER BY [id]) as [prev_timestamp],
   datediff(second, [timestamp], LAG([timestamp]) OVER (ORDER BY [id])) as [timestamp_diff]
  from orders

)
,group_start AS 
(
  SELECT cte.*, 
   --establish the start of a group
   CASE WHEN timestamp_diff between -10 and -1 
        and (LAG(timestamp_diff) OVER (ORDER BY id) < -10
          or LAG(timestamp_diff) OVER (ORDER BY id) > -1)
      THEN 1 
    END as group_start_ind
  FROM cte
)
,group_ind AS
(
  SELECT group_start.*,
   --give each group an id for final partitioning  
   sum(group_start_ind) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as group_id
  FROM group_start
)
SELECT group_ind.*,
  --for the first member of each group/partition, write the count of records meeting the condition
  CASE WHEN group_start_ind IS NOT NULL
     THEN count(CASE WHEN timestamp_diff BETWEEN -10 AND -1 THEN 1 END) OVER (PARTITION BY group_id)
     END as outpt
FROM group_ind
ORDER BY id

dbfiddle here