CODEWITHSUNDEEP
sqlgoogle-bigquery
Vlad Miller
Vlad Miller

Reputation: 2279

Calculate time between two actions within session

I have a following table structure

id | sessionId | event | created_on
---|-----------|-------|--------------------
 1 | 1         | view  | 2016-01-01 12:24:01
 2 | 1         | buy   | 2016-01-01 12:25:05
 3 | 2         | view  | 2016-01-01 12:25:09
 4 | 1         | view  | 2016-01-01 12:27:10
......

I'm trying to get time between two events, in this particular case I want to know how much time have passed between FIRST view and FIRST buy event within a session.

How do I apply WHERE for data within GROUP BY? Basically I want to group by sesssionID and get only sessions which include both view and buy actions and I want to get time between FIRST view and FIRST buy event for a single session.

How do I achieve required result?

Thank you

Upvotes: 2

Views: 644

Answers (3)

Zahiro Mor
Zahiro Mor

Reputation: 1718

A simple solution could be: Take the minimum created_at per sessionID where event is view and join it with the minimum created at per sessionID where event is buy. Use inner join to have only records with both...

select A.sessionID,A.firstView,B.firstBuy, datedif(depend on rdbms) from

(select sessionID,min(created_on) as firstView from tblName where event ='view' group by sessionID ) A

Inner join

(select sessionID,min(created_on) as firstBuy from tblName where event ='buy' group by sessionID ) B

On A.sessionID = B.sessionID

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

If you want to avoid extra JOINs - try below 

SELECT 
  sessionId, 
  TIMESTAMP_TO_SEC(firstBuy) - TIMESTAMP_TO_SEC(firstView) AS sec_to_buy
FROM (
  SELECT sessionId, 
    MIN(CASE WHEN event = 'view' THEN created_on END) AS firstView,
    MIN(CASE WHEN event = 'buy' THEN created_on END) AS firstBuy
  FROM (
    SELECT * FROM 
      (SELECT 1 AS sessionId, 'view' AS event, TIMESTAMP('2016-01-01 12:24:01') AS created_on),
      (SELECT 1 AS sessionId, 'buy' AS event,  TIMESTAMP('2016-01-01 12:25:05') AS created_on),
      (SELECT 2 AS sessionId, 'view' AS event, TIMESTAMP('2016-01-01 12:25:09') AS created_on),
      (SELECT 1 AS sessionId, 'view' AS event, TIMESTAMP('2016-01-01 12:27:10') AS created_on),
  )
  GROUP BY sessionId
  HAVING firstView <= firstBuy
)

Upvotes: 2

Serg
Serg

Reputation: 22811

Rather common sql syntax which hopefully google-bigquery supports 

select sessionId, 
  min(case event when 'view' then created_on end) as firstView,
  min(case event when 'buy' then created_on end) as firstBuy
from t
where event in ('view', 'buy')
group by sessionId
having max(event) != min(event)

Upvotes: 3

Related Questions