create table user_log (user_id varchar2(20), log_date date)Table created.
insert into user_log values ('User 1', to_date('14-JUN-14 09:00:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 2', to_date('14-JUN-14 09:05:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 1', to_date('14-JUN-14 09:21:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 1', to_date('14-JUN-14 10:20:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 1', to_date('14-JUN-14 10:30:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 1', to_date('14-JUN-14 12:30:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 1', to_date('14-JUN-14 12:55:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 1', to_date('14-JUN-14 13:20:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 1', to_date('14-JUN-14 15:20:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 2', to_date('14-JUN-14 09:34:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
insert into user_log values ('User 2', to_date('14-JUN-14 11:00:00', 'DD-MON-YY HH24:MI:SS'))1 row(s) inserted.
select user_id, to_char(log_date, 'DD-MON-YY HH24:MI:SS') log_date from user_log| USER_ID | LOG_DATE | User 2 | 14-JUN-14 09:05:00 | User 1 | 14-JUN-14 09:21:00 | User 1 | 14-JUN-14 10:20:00 | User 1 | 14-JUN-14 10:30:00 | User 1 | 14-JUN-14 12:30:00 | User 1 | 14-JUN-14 12:55:00 | User 1 | 14-JUN-14 13:20:00 | User 1 | 14-JUN-14 15:20:00 | User 2 | 14-JUN-14 09:34:00 | User 2 | 14-JUN-14 11:00:00 | User 1 | 14-JUN-14 09:00:00 |
|---|
select user_id, to_char(log_date, 'DD-MON-YY HH24:MI:SS') sess_time,
case when (log_date - nvl(lag(log_date) over
(partition by user_id order by log_date),
log_date - 1/24)) * 24 * 60 >= 30 then 1 else 0 end new_session
from user_log| USER_ID | SESS_TIME | NEW_SESSION | User 1 | 14-JUN-14 09:00:00 | 1 | User 1 | 14-JUN-14 09:21:00 | 0 | User 1 | 14-JUN-14 10:20:00 | 1 | User 1 | 14-JUN-14 10:30:00 | 0 | User 1 | 14-JUN-14 12:30:00 | 1 | User 1 | 14-JUN-14 12:55:00 | 0 | User 1 | 14-JUN-14 13:20:00 | 0 | User 1 | 14-JUN-14 15:20:00 | 1 | User 2 | 14-JUN-14 09:05:00 | 1 | User 2 | 14-JUN-14 09:34:00 | 0 | User 2 | 14-JUN-14 11:00:00 | 1 |
|---|
select user_id, sess_time,
sum(new_session)
over (partition by user_id order by sess_time) sess_no
from (
select user_id, to_char(log_date, 'DD-MON-YY HH24:MI:SS') sess_time,
case when (log_date - nvl(lag(log_date) over (partition by user_id order by log_date),
log_date - 1/24)) * 24 * 60 >= 30 then 1 else 0 end new_session
from user_log)| USER_ID | SESS_TIME | SESS_NO | User 1 | 14-JUN-14 09:00:00 | 1 | User 1 | 14-JUN-14 09:21:00 | 1 | User 1 | 14-JUN-14 10:20:00 | 2 | User 1 | 14-JUN-14 10:30:00 | 2 | User 1 | 14-JUN-14 12:30:00 | 3 | User 1 | 14-JUN-14 12:55:00 | 3 | User 1 | 14-JUN-14 13:20:00 | 3 | User 1 | 14-JUN-14 15:20:00 | 4 | User 2 | 14-JUN-14 09:05:00 | 1 | User 2 | 14-JUN-14 09:34:00 | 1 | User 2 | 14-JUN-14 11:00:00 | 2 |
|---|
select user_id, log_date, sess_time,
sum(new_session)
over (partition by user_id order by sess_time) sess_no
from (
select user_id, log_date, to_char(log_date, 'DD-MON-YY HH24:MI:SS') sess_time,
case when (log_date - nvl(lag(log_date) over (partition by user_id order by log_date),
log_date - 1/24)) * 24 * 60 >= 30 then 1 else 0 end new_session
from user_log)| USER_ID | LOG_DATE | SESS_TIME | SESS_NO | User 1 | 14-JUN-14 | 14-JUN-14 09:00:00 | 1 | User 1 | 14-JUN-14 | 14-JUN-14 09:21:00 | 1 | User 1 | 14-JUN-14 | 14-JUN-14 10:20:00 | 2 | User 1 | 14-JUN-14 | 14-JUN-14 10:30:00 | 2 | User 1 | 14-JUN-14 | 14-JUN-14 12:30:00 | 3 | User 1 | 14-JUN-14 | 14-JUN-14 12:55:00 | 3 | User 1 | 14-JUN-14 | 14-JUN-14 13:20:00 | 3 | User 1 | 14-JUN-14 | 14-JUN-14 15:20:00 | 4 | User 2 | 14-JUN-14 | 14-JUN-14 09:05:00 | 1 | User 2 | 14-JUN-14 | 14-JUN-14 09:34:00 | 1 | User 2 | 14-JUN-14 | 14-JUN-14 11:00:00 | 2 |
|---|
select user_id, sess_no, (max(log_date) - min(log_date)) * 24 * 60 session_len from (
select user_id, log_date, sess_time,
sum(new_session)
over (partition by user_id order by sess_time) sess_no
from (
select user_id, log_date, to_char(log_date, 'DD-MON-YY HH24:MI:SS') sess_time,
case when (log_date - nvl(lag(log_date) over (partition by user_id order by log_date),
log_date - 1/24)) * 24 * 60 >= 30 then 1 else 0 end new_session
from user_log)
) group by user_id, sess_no
| USER_ID | SESS_NO | SESSION_LEN | User 1 | 1 | 20.99999999999999999999999999999999999995 | User 1 | 3 | 49.99999999999999999999999999999999999997 | User 2 | 1 | 29.00000000000000000000000000000000000002 | User 2 | 2 | 0 | User 1 | 2 | 10 | User 1 | 4 | 0 |
|---|
select user_id, sess_no, round((max(log_date) - min(log_date)) * 24 * 60) session_len from (
select user_id, log_date, sess_time,
sum(new_session)
over (partition by user_id order by sess_time) sess_no
from (
select user_id, log_date, to_char(log_date, 'DD-MON-YY HH24:MI:SS') sess_time,
case when (log_date - nvl(lag(log_date) over (partition by user_id order by log_date),
log_date - 1/24)) * 24 * 60 >= 30 then 1 else 0 end new_session
from user_log)
) group by user_id, sess_no| USER_ID | SESS_NO | SESSION_LEN | User 1 | 1 | 21 | User 1 | 3 | 50 | User 2 | 1 | 29 | User 2 | 2 | 0 | User 1 | 2 | 10 | User 1 | 4 | 0 |
|---|
select count(*),
case when session_len <= 10 then 10
when session_len <= 20 then 20
when session_len <= 30 then 30
when session_len <= 40 then 40
when session_len <= 50 then 50
else 60 end sess_len
from (
select user_id, sess_no, round((max(log_date) - min(log_date)) * 24 * 60) session_len from (
select user_id, log_date, sess_time,
sum(new_session)
over (partition by user_id order by log_date) sess_no
from (
select user_id, log_date, to_char(log_date, 'DD-MON-YY HH24:MI:SS') sess_time,
case when (log_date - nvl(lag(log_date) over (partition by user_id order by log_date),
log_date - 1/24)) * 24 * 60 >= 30 then 1 else 0 end new_session
from user_log)
) group by user_id, sess_no
)
group by case when session_len <= 10 then 10
when session_len <= 20 then 20
when session_len <= 30 then 30
when session_len <= 40 then 40
when session_len <= 50 then 50
else 60 end| COUNT(*) | SESS_LEN | 1 | 50 | 2 | 30 | 3 | 10 |
|---|