CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Alireza
Alireza

Reputation: 5673

Get session duration averages over time spans

The table structure is like this:

table name : log

session_id                              event_type      time
-------------------------------------------------------------------------------------
6983bb14-09e1-4697-ac6f-1528bb252a07    session_start   2019-09-12 13:40:18.307411+00
6983bb14-09e1-4697-ac6f-1528bb252a07    session_end     2019-09-12 13:41:42.000399+00
38866490-c1d6-4036-a7f9-c9cfa9bf23b4    session_start   2019-09-12 14:07:35.34742+00
38866490-c1d6-4036-a7f9-c9cfa9bf23b4    session_end     2019-09-12 14:09:01.873879+00
c00d41ae-6e1c-4b52-8bcc-009cfcc96c5a    session_start   2019-09-12 14:09:51.940051+00
c00d41ae-6e1c-4b52-8bcc-009cfcc96c5a    session_end     2019-09-12 14:11:02.085879+00
9c07101e-f09d-43ac-ba86-b92ea19e2d22    session_start   2019-09-12 14:15:43.66439+00
9c07101e-f09d-43ac-ba86-b92ea19e2d22    session_end     2019-09-12 14:18:17.38121+00
...

I am trying to query the table to get the following desired result:

day           avg_session_duration(seconds)
--------------------------------------------
2019-09-12   1200
2019-09-13   800
...

Upvotes: 1

Views: 42

Answers (2)

GMB
GMB

Reputation: 222512

You can aggregate twice:

select 
    session_time::date "day",
    avg(session_duration_seconds) avg_session_duration_seconds
from (
    select 
        session_id,
        min(time) session_time,
        extract(epoch from (max(time) - min(time))) session_duration_seconds
    from log
    group by session_id
) t
group by session_time::date
order by session_time::date

This works under the following assumption:

  • two distinct sessions never share the same session_id
  • a given session has two and only two entries in the table, the first corresponding to the start of the session and the second one to its end
  • column time is of timestamp datatype (otherwise you need to cast it first)

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

left join and group by will achieve this requirement.

select t1.session_time
    , avg(t2.ss - t1.ss)  as avg_session_duration
from (
    select session_time::date as dt
        , sum(extract(epoch from session_time::time)) ss
    from 
    tableA 
    where event_type = 'session_start'
    group by session_time::date) as t1
left join(
    select session_time::date as dt
        , sum(extract(epoch from session_time::time)) ss
    from 
    tableA  
    where event_type = 'session_end'
    group by session_time::date) as t2 on t2.dt = t1.dt

Upvotes: 1

Related Questions