[SQL문제풀기] Users By Average Session Time

문제

StrataScratch - Users By Average Session Time

StrataScratch

https://platform.stratascratch.com/coding/10352-users-by-avg-session-time?code_type=3

내가 작성한 정답

MySQL

select user_id, avg(st)
from (select user_id, date(timestamp) "date",
                min(case when action = 'page_exit' then time_to_sec(time(timestamp)) end)
                - max(case when action = 'page_load' then time_to_sec(time(timestamp)) end) st
        from facebook_web_log
        group by user_id, date(timestamp)) a
where st > 0
group by user_id; 

PostgreSQL

select user_id, avg(st)
from (select user_id, date(timestamp) "date",
                min(case when action = 'page_exit' then extract(epoch from timestamp) end)
                - max(case when action = 'page_load' then extract(epoch from timestamp) end) st
        from facebook_web_log
        group by user_id, date(timestamp)) a
where st > 0
group by user_id; 

Oracle

with a as (select user_id, trunc(timestamp) dat, action,
                  extract(hour from timestamp)*3600 + extract(minute from timestamp)*60 +extract(second from timestamp) as sec
           from facebook_web_log),
     b as (select user_id, dat,
                min(case when action = 'page_exit' then sec end) - max(case when action ='page_load' then sec end) as dif
            from a
            group by user_id, dat)
select user_id, avg(dif)
from b
where dif > 0
group by user_id;