CODEWITHSUNDEEP
sqlamazon-redshift
Si_CPyR
Si_CPyR

Reputation: 161

Calculating time spent in a session with rules

Let's say the base table contains information about each action (each row) a customer takes during an their app session. When each session starts, the action='Show' is recorded, and when the user ends the session, the action='Hide' is recorded. And for any other action an user does, is recorded in between Show and Hide with different action names. Unfortunately there's no sessionID column, which is what I am trying to create based on these information.

Please assume the two actions 'Show' and 'Hide' only are emitted when session starts and when session ends.

With the given information, I'd like to summarize the data by creating unique sessionID for each session (could be numbers or strings) and record the time spent in the session.

I am using Redshift SQL, so if there's any special function in Redshift I can use, please leverage it.

base table and desired output

Upvotes: 1

Views: 302

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to write two subqueries and make row number by customer

one for action = 'show', another for action = 'Hide'.

then join by row number which means time start and end.

SELECT t1.customer,
       t2.rn,  
       DATEDIFF(mins, t1.time,t2.time) spentTime
FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY customer ORDER BY time) rn 
    FROM bastTable 
    WHERE action = 'show'
) t1 JOIN (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY customer ORDER BY time) rn  
    FROM bastTable 
    WHERE action = 'Hide'
) t2 on t1.rn = t2.rn and t1.customer = t2.customer

Upvotes: 1

Related Questions