MSSQL select count entities in date range

Question

I have a task to find how often a user performs actions per a minute. I have found the solution with grouping by date part:

CREATE TABLE UserAction(Id int, Date datetime, Action nvarchar(200))
Go
INSERT INTO UserAction VALUES
    (111, '2000-01-01 15:30:10', 'Action Made'),
    (555, '2000-01-01 15:30:10', 'Action Made'), 
    (111, '2000-01-01 15:30:20', 'Action Made'),
    (111, '2000-01-01 15:31:05', 'Action Made'),
    (111, '2000-01-01 15:31:10', 'Action Made'),
    (555, '2000-01-01 15:31:10', 'Action Made'),
    (111, '2000-01-01 15:32:05', 'Action Made'),
    (555, '2000-01-01 15:32:05', 'Action Made'),
    (555, '2000-01-03 15:35:10', 'Action Made'),
    (555, '2000-01-03 15:35:20', 'Action Made'),
    (111, '2000-01-03 15:36:05', 'Action Made'),
    (555, '2000-01-03 15:36:05', 'Action Made')
GO

-- query 
SELECT 
    Id,
    MIN(Date) as [From],
    MAX(Date) as [To],
    COUNT(*) as [Count]
FROM 
    UserAction
GROUP BY 
    ID,
    DATEADD(MINUTE, 1 + (DATEDIFF(MINUTE, 0, Date) / 1) * 1, 0)
ORDER BY
    Id

-- should be
SELECT 
    * 
FROM 
    (VALUES
        (111, '2000-01-01 15:30:10.000', '2000-01-01 15:31:05.000', 3),
        (111, '2000-01-01 15:31:10.000', '2000-01-01 15:32:05.000', 2),
        (111, '2000-01-03 15:36:05.000', '2000-01-03 15:36:05.000', 1),
        (111, '2000-01-01 15:30:10.000', '2000-01-01 15:30:10.000', 1),
        (111, '2000-01-01 15:31:10.000', '2000-01-01 15:32:05.000', 2),
        (111, '2000-01-03 15:35:10.000', '2000-01-03 15:36:05.000', 3)
    )
as _shouldBe(Id, [From], [To], [Count])

But I cannot find out how to group by datediff in a minute(60 seconds). The result now:

Id          From                    To                      Count
----------- ----------------------- ----------------------- -----------
111         2000-01-01 15:30:10.000 2000-01-01 15:30:20.000 2
111         2000-01-01 15:31:05.000 2000-01-01 15:31:10.000 2
111         2000-01-01 15:32:05.000 2000-01-01 15:32:05.000 1
111         2000-01-03 15:36:05.000 2000-01-03 15:36:05.000 1
555         2000-01-01 15:30:10.000 2000-01-01 15:30:10.000 1
555         2000-01-01 15:31:10.000 2000-01-01 15:31:10.000 1
555         2000-01-01 15:32:05.000 2000-01-01 15:32:05.000 1
555         2000-01-03 15:35:10.000 2000-01-03 15:35:20.000 2
555         2000-01-03 15:36:05.000 2000-01-03 15:36:05.000 1

How it should be:

Id          From                    To                      Count
----------- ----------------------- ----------------------- -----------
111         2000-01-01 15:30:10.000 2000-01-01 15:31:05.000 3
111         2000-01-01 15:31:10.000 2000-01-01 15:32:05.000 2
111         2000-01-03 15:36:05.000 2000-01-03 15:36:05.000 1
111         2000-01-01 15:30:10.000 2000-01-01 15:30:10.000 1
111         2000-01-01 15:31:10.000 2000-01-01 15:32:05.000 2
111         2000-01-03 15:35:10.000 2000-01-03 15:36:05.000 3

I would be grateful if you could help me with finding the best solution here.

Answer 1

You should group by the result of division by 60 starting by the minimal date per Id:

declare @UserAction table (Id int, Date datetime, Action nvarchar(200))

INSERT INTO @UserAction VALUES
    (111, '2000-01-01 15:30:10', 'Action Made'),
    (555, '2000-01-01 15:30:10', 'Action Made'), 
    (111, '2000-01-01 15:30:20', 'Action Made'),
    (111, '2000-01-01 15:31:05', 'Action Made'),
    (111, '2000-01-01 15:31:10', 'Action Made'),
    (555, '2000-01-01 15:31:10', 'Action Made'),
    (111, '2000-01-01 15:32:05', 'Action Made'),
    (555, '2000-01-01 15:32:05', 'Action Made'),
    (555, '2000-01-03 15:35:10', 'Action Made'),
    (555, '2000-01-03 15:35:20', 'Action Made'),
    (111, '2000-01-03 15:36:05', 'Action Made'),
    (555, '2000-01-03 15:36:05', 'Action Made');

with cte AS
(
select *, 
       min(Date) over(partition by id) as min_dt
from @UserAction 
)

,cte_gr AS
(
select *,
       datediff(ss, min_dt, Date) / 60 as gr
from cte
)

select id, 
       min(date) as [From],
       max(date) as [To],
       count(*) as cnt
from cte_gr
group by id, gr;