I would like to ask if anyone could help me with this. I have a table with user traffic on websites. The important column is Timestamp which looks like this.
| Timestamp | | 2017-06-26 23:52:09.000 | | 2017-06-26 23:52:22.000 | | 2017-06-26 23:58:13.000 | | 2017-06-26 23:52:59.000 | | 2017-06-27 00:02:35.000 |
Timestamps are used to calculate sessions. An hour or so in timestamps means one session. The problem is that calculated session is different when the activity on the websites was made after midnight. Thus, the example below makes two sessions, not one.
| 2017-06-26 23:52:59.000 | | 2017-06-27 00:02:35.000 |
I need to find and merge or somehow connect one Timestamp (after midnight) to others (after midnight). Thank you for any advice. I have been trying for a while but I can´t make any progress.
Advertisement
Answer
If I understand correctly, you want to assign sessions based on a gap of an hour in the timestamps.
This is easily done with window functions:
select t.*,
sum(case when timestamp < dateadd(hour, 1, prev_timestamp) then 0
else 1
end) over (partition by user_id order by timestamp) as session_number
from (select t.*, lag(timestamp) over (partition by user_id order by timestamp) as prev_timestamp
from t
) t;
I am speculating on the existence of a user id and that you want to do this per user.