0

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.

1
  • Microsoft SQL Server 2016 Jan 16, 2018 at 7:59

1 Answer 1

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;
1
  • Thank you for the answer. I am checking it now and it looks good! Jan 16, 2018 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.