POSTGRESQL – How to sum (date row 0 – date row 1) + (date row 2 – date row 3)

postgresql

I would like to know how I could make a sum of (date row 0 – date row 1) + (date row 2 – date row 3) and so on… group by userId.

Please refer to the screenshot below, I want to sum the time between every APP_OPENED and APP_QUIT event for EACH different idUser that logs into the app.

enter image description here

Result must be something like :

idUser – diffTime

2cf3… – 25 seconds

504… – 50 secondes

Best Answer

create table events (event varchar(30), content json, event_date timestamp);
insert into events values
('APP_OPENED',          '{"connected":true,"idUser":"1"}', '2017-04-26 12:00:58.176'),
('APP_QUIT',            '{"connected":true,"idUser":"1"}', '2017-04-26 12:01:08.151'),
('APP_OPENED',          '{"connected":true,"idUser":"2"}', '2017-04-26 12:01:11.595'),
('APP_QUIT',            '{"connected":true,"idUser":"2"}', '2017-04-26 12:01:28.144'),
('APP_OPENED',          '{"connected":true,"idUser":"3"}', '2017-04-26 12:01:34.51'),
('APP_QUIT',            '{"connected":true,"idUser":"3"}', '2017-04-26 12:01:40.974'),
('APP_OPENED',          '{"connected":true,"idUser":"4"}', '2017-04-26 12:14:30.689'),
('APP_USER_LOGGING_IN', '{"userId":"5"}',                  '2017-04-26 12:16:41.905'),
('APP_QUIT',            '{"connected":true,"idUser":"5"}', '2017-04-26 12:16:49.287'),
('APP_OPENED',          '{"connected":true,"idUser":"6"}', '2017-04-26 12:16:51.115'),
('APP_QUIT',            '{"connected":true,"idUser":"6"}', '2017-04-26 12:17:26.866');

According to you question I've considered that each action if formed by two events:

(APP_OPENED & APP_QUIT) or (APP_USER_LOGGING_IN & APP_QUIT)

The first step is to flag one reset point for every APP_OPENED or APP_USER_LOGGING_IN, that is the event that starts one event, and then assign a different group to every couple of events.

--= Set reset points
with setReset as 
(
    select   event, 
             case when content->>'idUser' is null then content->>'userId' else content->>'idUser' end userId,
             event_date,
             case when event <> 'APP_QUIT' then 1 end reset_point
    from     events
    order by event_date
)
    --= Generates groups
    , setGrp as
    (
        select   event, userId, event_date, 
                 count(reset_point) over (order by event_date) grp
        from     setReset
        order by event_date
    )
    select * from setGrp;
event               | userid | event_date              | grp
:------------------ | :----- | :---------------------- | --:
APP_OPENED          | 1      | 2017-04-26 12:00:58.176 |   1
APP_QUIT            | 1      | 2017-04-26 12:01:08.151 |   1
APP_OPENED          | 2      | 2017-04-26 12:01:11.595 |   2
APP_QUIT            | 2      | 2017-04-26 12:01:28.144 |   2
APP_OPENED          | 3      | 2017-04-26 12:01:34.51  |   3
APP_QUIT            | 3      | 2017-04-26 12:01:40.974 |   3
APP_OPENED          | 4      | 2017-04-26 12:14:30.689 |   4
APP_USER_LOGGING_IN | 5      | 2017-04-26 12:16:41.905 |   5
APP_QUIT            | 5      | 2017-04-26 12:16:49.287 |   5
APP_OPENED          | 6      | 2017-04-26 12:16:51.115 |   6
APP_QUIT            | 6      | 2017-04-26 12:17:26.866 |   6

Finally you can get time difference of each group:

--= Set reset points
with setReset as 
(
    select   event, 
             case when content->>'idUser' is null then content->>'userId' else content->>'idUser' end userId,
             event_date,
             case when event <> 'APP_QUIT' then 1 end reset_point
    from     events
    order by event_date
)
    --= Generates groups
    , setGrp as
    (
        select   event, userId, event_date, 
                 count(reset_point) over (order by event_date) grp
        from     setReset
        order by event_date
    )
    ---= Calculate datediff of each group
    select userid, extract(epoch from(max(event_date) - min(event_date))) 
    from   setGrp
    group by grp, userId
    having   count(grp) > 1
    order by grp;
userid | date_part
:----- | :--------
1      | 9.975    
2      | 16.549   
3      | 6.464    
5      | 7.382    
6      | 35.751   

dbfiddle here