How to calculate average time between entries grouped by date and user in SQL Server?

I have data that looks like:

User   Start_Time             End_Time
-------------------------------------------------
A      2018-12-05 08:30:00    2018-12-05 09:45:00
B      2018-12-05 09:30:00    2018-12-05 10:00:00
B      2018-12-05 10:30:00    2018-12-05 10:45:00
A      2018-12-05 10:45:00    2018-12-05 11:45:00
A      2018-12-05 12:15:00    2018-12-05 12:30:00
A      2018-12-06 05:30:00    2018-12-05 06:45:00
B      2018-12-06 06:45:00    2018-12-05 07:45:00
B      2018-12-06 08:45:00    2018-12-05 09:30:00

I am trying to calculate the average length of time between the End_Time of one entry and the Start_time of the next entry, grouped by the user and the date.

From the example above the output should look like:

User  Date         Average Time Between Entries(in minutes)
------------------------------------------------------------
A     2018-12-05   45
B     2018-12-05   30
A     2018-12-06   Null
B     2018-12-06   60

Is there a SQL Server query that could accomplish this?

Answer

Use lead():

select t.user, convert(date, start_time),
       avg(datediff(minute, end_time, next_start_time))
from (select t.*,
             lead(start_time) over (partition by user, convert(date, start_time) order by start_time) as next_start_time
      from t
     ) t
group by user, convert(date, start_time);

Here is a db<>fiddle (with the data fixed so the end times are greater than the start times).

Advertisement

Answer