I have a Sessions table with the columns (User_ID, Sessions_ID, LogOn, LogOut), the user can open more than one session in the same time, my goal is to calculate the pure time spent on my system for each user. I used the following query:
SELECT
T1.User_ID,
SUM(T1.Duration) AS Duration
FROM (
SELECT
T2.User_ID,
T2.Logon,
(CASE WHEN T3.LogOn IS NULL OR T3.LogOn > T2.LogOut THEN T2.LogOut ELSE T3.LogOn END) AS LogOutEdited,
DATEDIFF(MINUTE, T2.Logon, (CASE WHEN T3.LogOn IS NULL OR T3.LogOn > T2.LogOut THEN T2.LogOut ELSE T3.LogOn END)) AS Duration
FROM (
SELECT
(DENSE_RANK() OVER (PARTITION BY User_ID ORDER BY LogOn)) AS Serial,
User_ID, LogOn, LogOut
FROM Sessions
) AS T2
LEFT JOIN (
SELECT
(DENSE_RANK() OVER (PARTITION BY User_ID ORDER BY LogOn)) AS Serial,
User_ID, LogOn, LogOut
FROM Sessions
) AS T3
ON T2.User_ID = T3.User_ID
AND T2.Serial = T3.Serial - 1
) AS T1
GROUP BY T1.User_ID
This query compares the end of a session with the start of its next one, and adjusts the end of the first one in order to remove the overlapping time. It does give correct results (I think :) ) but its performance is not appreciated, is there a more efficient logic I can apply here?
EDIT:
Sample Data:
--------------------------------------------------------------------
| User_ID | Session_ID | LogOn | LogOut |
--------------------------------------------------------------------
| 1 | 100 | 2020-01-01 01:00:00 | 2020-01-01 01:30:00 |
--------------------------------------------------------------------
| 1 | 101 | 2020-01-01 01:15:00 | 2020-01-01 01:45:00 |
--------------------------------------------------------------------
| 1 | 102 | 2020-01-01 01:35:00 | 2020-01-01 01:40:00 |
--------------------------------------------------------------------
| 2 | 103 | 2020-01-01 03:13:00 | 2020-01-01 03:23:00 |
--------------------------------------------------------------------
| 1 | 104 | 2020-01-01 04:00:00 | 2020-01-01 04:15:00 |
--------------------------------------------------------------------
Desired Results:
----------------------
| User_ID | Duration |
----------------------
| 1 | 60 |
----------------------
| 2 | 10 |
----------------------
Undesired Results:
----------------------
| User_ID | Duration |
----------------------
| 1 | 80 |
----------------------
| 2 | 10 |
----------------------
This is a gaps-and-island problem, where you are trying to identify the islands, and sum their overall duration for each user.
Here is an approach using lag()
and a window sum()
to define the groups. The following query gives you one row for each group of overlapping sessions:
select user_id, min(log_in) log_in, max(log_out) log_out
from (
select
t.*,
sum(case when log_in <= lag_log_out then 0 else 1 end)
over(partition by user_id order by log_in) as grp
from (
select
t.*,
lag(log_out) over(partition by user_id order by log_in) as lag_log_out
from mytable t
) t
) t
group by user_id, grp
You can add one level of aggregation to compute the overall time spent per user:
select user_id, sum(datediff(minute, login, log_out)) duration
from (
select user_id, min(log_in) log_in, max(log_out) log_out
from (
select
t.*,
sum(case when log_in <= lag_log_out then 0 else 1 end)
over(partition by user_id order by log_in) as grp
from (
select
t.*,
lag(log_out) over(partition by user_id order by log_in) as lag_log_out
from mytable t
) t
) t
group by user_id, grp
) t
group by user_id