By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t
(Username varchar(5), Event_time datetime, Is_new_session varchar(5))
;
INSERT INTO t
(Username, Event_time, Is_new_session)
VALUES
('userA', '2022-09-30 00:00:01', 'True'),
('userA', '2022-09-30 00:00:02', 'False'),
('userA', '2022-09-30 01:00:00', 'True'),
('userA', '2022-09-30 02:00:00', 'True'),
('userA', '2022-09-30 02:00:02', 'False'),
('userA', '2022-09-30 02:00:04', 'False'),
('userA', '2022-09-30 02:00:05', 'False'),
('userB', '2022-09-30 03:00:00', 'True')
;
8 rows affected
select *
from t
| Username | Event_time | Is_new_session |
|---|---|---|
| userA | 2022-09-30 00:00:01.000 | True |
| userA | 2022-09-30 00:00:02.000 | False |
| userA | 2022-09-30 01:00:00.000 | True |
| userA | 2022-09-30 02:00:00.000 | True |
| userA | 2022-09-30 02:00:02.000 | False |
| userA | 2022-09-30 02:00:04.000 | False |
| userA | 2022-09-30 02:00:05.000 | False |
| userB | 2022-09-30 03:00:00.000 | True |
select session_id
,Username
,min(Event_time) as First_Event_time
,max(Event_time) as Last_Event_time
,count(*) as Event_counts
from (
select *
,count(case Is_new_session when 'True' then 1 end) over(order by Event_time) as session_id
from t
) t
group by Username, session_id
| session_id | Username | First_Event_time | Last_Event_time | Event_counts |
|---|---|---|---|---|
| 1 | userA | 2022-09-30 00:00:01.000 | 2022-09-30 00:00:02.000 | 2 |
| 2 | userA | 2022-09-30 01:00:00.000 | 2022-09-30 01:00:00.000 | 1 |
| 3 | userA | 2022-09-30 02:00:00.000 | 2022-09-30 02:00:05.000 | 4 |
| 4 | userB | 2022-09-30 03:00:00.000 | 2022-09-30 03:00:00.000 | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.