CODEWITHSUNDEEP
sqlamazon-redshift
Alex Nikitin
Alex Nikitin

Reputation: 524

Modify SQL query to show user session length

I have a table that looks like this:

user_id   user_action      timestamp
1         action #2     2016-02-01 00:02  
2         action #1     2017-10-05 15:24
3         action #3     2017-03-31 19:35
4         action #1     2017-07-09 00:24
1         action #1     2018-11-05 18:28
1         action #3     2018-02-01 13:02
2         action #2     2017-10-05 16:14
2         action #3     2017-10-05 16:34
etc

I have a query that identifies user sessions, which are action #1, #2 and #3, made in a time period less than one hour from each other. Example of a session:

2         action #1     2017-10-05 15:24
2         action #2     2017-10-05 16:14
2         action #3     2017-10-05 16:34

My query so far looks like this and shows user_id of users, who had sessions:

select distinct user_id
from (select user_id,user_action,timestamp,
      lag(user_action) over(partition by user_id order by timestamp) as prev_action,
      lead(user_action) over(partition by user_id order by timestamp) as next_action,
      datediff(minute,lag(timestamp) over(partition by user_id order by timestamp),timestamp) as time_diff_with_prev_action,
      datediff(minute,timestamp,lead(timestamp) over(partition by user_id order by timestamp)) as time_diff_with_next_action
      from tbl
     ) t
where user_action='action#2' and prev_action='action#1' and next_action='action#3'
and time_diff_with_prev_action <= 60 and time_diff_with_next_action <= 60

What I need is to edit a query, add 2 columns to the output, session start time and session end time, which is last action + 1 hour. Please advice how to make it. Temporary tables are forbidden, so it should be just a query. Thanks for your time!

Upvotes: 0

Views: 127

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37347

Try this:

;with cte as (
    select [user_id],
           cast(RIGHT([user_action], 1) as int) [action_id],
           [timestamp]
    from @table
),cte2 as (
    select *,
           (select [timestamp] from cte
            where C.[user_id] = [user_id] and C.[action_id] + 1 = [action_id]
              and DATEDIFF(minute, C.[timestamp], [timestamp]) between 0 and 60) [nextActionTimestamp]
    from cte [C]
)

select [user_id],
       [action_id],
       [timestamp] [SessionStart],
       dateadd(hour, 1, [nextNextActionTimestamp]) [SessionEnd]
from (
select *,
       (select [nextActionTimestamp] from cte2
        where C.[user_id] = [user_id] and C.[action_id] + 1 = [action_id]
          and DATEDIFF(minute, C.[nextActionTimestamp], [nextActionTimestamp]) between 0 and 60) [nextNextActionTimestamp]
from cte2 [C]
) a
where [nextActionTimestamp] is not null and [nextNextActionTimestamp] is not null
order by [user_id],[action_id]

Upvotes: 1

Related Questions