Remove Time Overlaps in Sessions Records

Question

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    |
----------------------

Provide sample data and desired results. – Gordon Linoff Jun 22 '20 at 23:20 — Gordon Linoff, Jun 22 '20 at 23:20

score 1 · Accepted Answer · answered Jun 22 '20 at 23:19

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

@GMP, Exactly what I needed, the LAG & LEAD functions will save me a lot of JOINs in future, do you suggest a source to study real life problems like this one (gaps and islands)? — A.Kallas, Jun 26 '20 at 23:31

Remove Time Overlaps in Sessions Records

1 Answers1