CODEWITHSUNDEEP
sqlsql-server
adrianvlupu
adrianvlupu

Reputation: 4618

Average distance between user visits

In the following scenario what would be the simplest way to get the average distance between the visits.

declare @LocationID int = 1;
declare @Visits table (
    UserID int,
    LocationID int,
    DateTimeIn datetime,
    DateTimeOut datetime
)
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (1, 1, '20150119 00:30', '20150119 00:45');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (1, 1, '20150119 00:50', '20150119 01:15');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (1, 1, '20150119 01:30', '20150119 02:00');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (1, 1, '20150119 02:05', '20150119 02:20');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (1, 1, '20150119 03:10', '20150119 03:15');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (2, 1, '20150119 00:32', '20150119 00:37');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (2, 1, '20150119 00:40', '20150119 00:55');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (2, 1, '20150119 01:53', '20150119 02:00');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (2, 1, '20150119 02:01', '20150119 02:15');
insert into @Visits (UserID, LocationID, DateTimeIn, DateTimeOut) values (2, 1, '20150119 03:10', '20150119 03:15');

My guess was this:

WITH CTE as (
    select UserID, DateTimeIn, DateTimeOut, RANK() OVER (PARTITION BY UserID ORDER BY DateTimeIn asc) as 'RankNo'
    from @Visits
)
Select AVG(Seconds) as 'AvgRecency' From (
    SELECT A.UserID, ISNULL(DATEDIFF(SECOND, A.DateTimeOut, B.DateTimeIn), 0) as 'Seconds'
    FROM CTE A INNER JOIN
    CTE B on A.RankNo = B.RankNo-1 AND A.UserID = B.UserID
)A

But in a real program, using this causes a spill level one warning on the sort operation, even if there is an index containing userid and datetimein.

Is there any other way of doing this without a rank function ?

Upvotes: 0

Views: 100

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

If you are using SQL SERVER 2012+ then you can try using LEAD function:

;WITH CTE AS (
   SELECT UserID, DateTimeOut, LEAD(DateTimeIn) OVER (PARTITION BY UserID ORDER BY DateTimeIn asc) as 'NextCheckIn'
   FROM @Visits
)
Select AVG(Seconds) as 'AvgRecency' From (
    SELECT UserID, ISNULL(DATEDIFF(SECOND, DateTimeOut, NextCheckIn), 0) as 'Seconds'
    FROM CTE 
    WHERE NextCheckIn IS NOT NULL
) A

Upvotes: 1

Related Questions