Set based query to group data ordered data

Posted on

Question :

I have a table of user action data and I would like to try and group it into ‘sessions’, so I could figure out how many times a user has logged on, how long they stay online, and how many actions they do during the time…

Full sql fiddle here:!6/e24a50/4

Basically I have a table defined below, and I would like to group the actions when there is less than 20minutes of activity between two requests

CREATE TABLE WebsiteUsage (
      UserId INT NOT NULL,
      ApplicationId VARCHAR(MAX) NOT NULL,

Ideally, I would get a resultset that resembled this:

UserId      Session     StartTime               EndTime                 ActionCount
----------- ----------- ----------------------- ----------------------- -----------
1           1           2015-04-15T10:28:03.887 2015-04-15T10:38:18.037 58
1           2           2015-04-15T14:40:26.277 2015-04-15T14:56:02.857 13

I’ve got the time between requests using the following query, but I cannot figure out how to group them based on when MinutesSinceLastRequest < 20

WITH cte AS (
    PreviousTimestamp = LAG([Timestamp], 1, NULL) OVER (PARTITION BY UserId, ApplicationId ORDER BY [Timestamp])
FROM dbo.WebsiteUsage
    MinutesSinceLastRequest = DATEDIFF(MINUTE, PreviousTimestamp, [Timestamp])
FROM cte

Is there a way to accomplish this in a set based operation or do I have to fall back to the old cursor?

Answer :

Start by making a column like CASE WHEN MinutesSinceLastRequest < 20 THEN 0 ELSE 1 END AS IsOver20

Then wrap that in a rolling sum – this will change every time there’s a 1 for IsOver20: SUM(IsOver20) OVER (ORDER BY [TimeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupNum

So now you can group by this easily. Include PARTITION BY UserId if you need to break it down by UserId too.

Leave a Reply

Your email address will not be published. Required fields are marked *