Average time spent by user by including all the login and logout made-CodePudding

I have log_event table and which has table as shown below:

user_id|event_date_time| event
------------------------------
7494212|1535308430     | Opened app
7494212|1535308433     | Closed app
1475185|1535308444     | Registered
6946725|1535308475     | Opened app
6946725|1535308476     | Made a purchase
6946725|1535308477     | closed app

Create table log_event (
    user_id int,
    event_date_time bigint,
    event varchar(70)
);

INSERT INTO log_event Values( 7494212,1535308430,'Opened app');
INSERT INTO log_event Values( 7494212,1535308433,'Closed app');
INSERT INTO log_event Values( 1475185,1535308444,'Registered');
INSERT INTO log_event Values( 6946725,1535308475,'Opened app');
INSERT INTO log_event Values( 6946725,1535308476,'Made a purchase');
INSERT INTO log_event Values( 6946725,1535308477,'closed app');

db<>fiddle

I want to find average time spent by user based on Event's column Open and close . there can be multiple open and close entry for single user in a day or month. Is there a way out since I am not sure how to find next close for every opened app event and then group by for single user? Thanks in advance

I have already looked in stackoverflow for solution but couldn't find anything closest to it.

CodePudding user response：

You can get the row with event = 'Closed app' for each with row with event = 'Opened app' with LEAD() window function and use it to find the difference of the 2 timestamps.
Then you can aggregate to get the averages:

SELECT user_id, AVG(time_dif) average_time_spent
FROM (
  SELECT user_id, event,
         LEAD(event_date_time) OVER (PARTITION BY user_id ORDER BY event_date_time) - event_date_time time_dif
  FROM log_event
  WHERE event IN ('Opened app', 'Closed app')
) t
WHERE event = 'Opened app'
GROUP BY user_id;

See the demo.

CodePudding user response：

You can use a self-join:

with sessions(id, c, d) as (
   select l.user_id, l.event_date_time, l.event_date_time - max(l1.event_date_time) 
   from log_event l join log_event l1 on l1.user_id = l.user_id and l1.event_date_time < l.event_date_time and lower(l1.event) = 'opened app' where lower(l.event) = "closed app" 
   group by l.user_id, l.event_date_time
)
select id, avg(d) from sessions group by id;