Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE labels(user_id INT, session_id INT,
  start_time TIMESTAMP,mode TEXT);

INSERT INTO labels (user_id,session_id,start_time,mode)
VALUES  (48,652,'2016-04-01 00:47:00+01','foot'),
(9,656,'2016-04-01 00:03:39+01','car'),(9,657,'2016-04-01 00:26:51+01','car'),
(9,658,'2016-04-01 00:45:19+01','car'),(46,663,'2016-04-01 00:13:12+01','car');

CREATE TABLE



INSERT 0 5


CREATE TABLE raw_data(user_id INT, session_id INT,timestamp TIMESTAMP) ;

INSERT INTO raw_data(user_id, session_id,timestamp)          
VALUES (8,652,'2016-04-01 00:46:11.638+01'),(8,652,'2016-04-01 00:47:00.566+01'),
       (8,652,'2016-04-01 00:48:06.383+01'),(9,656,'2016-04-01 00:14:17.707+01'),
       (9,656,'2016-04-01 00:15:18.664+01'),(9,656,'2016-04-01 00:16:19.687+01'),
       (9,656,'2016-04-01 00:24:20.691+01'),(9,656,'2016-04-01 00:25:23.681+01'),
       (9,657,'2016-04-01 00:24:50.842+01'),(9,657,'2016-04-01 00:26:51.096+01'),
       (9,657,'2016-04-01 00:37:54.092+01');

CREATE TABLE



INSERT 0 11


select * from raw_data; select * from labels


user_id
session_id
timestamp


8
652
2016-04-01 00:46:11.638

8
652
2016-04-01 00:47:00.566

8
652
2016-04-01 00:48:06.383

9
656
2016-04-01 00:14:17.707

9
656
2016-04-01 00:15:18.664

9
656
2016-04-01 00:16:19.687

9
656
2016-04-01 00:24:20.691

9
656
2016-04-01 00:25:23.681

9
657
2016-04-01 00:24:50.842

9
657
2016-04-01 00:26:51.096

9
657
2016-04-01 00:37:54.092



SELECT 11




user_id
session_id
start_time
mode


48
652
2016-04-01 00:47:00
foot

9
656
2016-04-01 00:03:39
car

9
657
2016-04-01 00:26:51
car

9
658
2016-04-01 00:45:19
car

46
663
2016-04-01 00:13:12
car



SELECT 5


select  session_id	  
       ,timestamp	
       ,user_id	
       ,start_time	
       ,count(diff) over() as number_of_sessions_with_problem
from   (
       select session_id     
             ,timestamp    
             ,labels.user_id  
             ,start_time   
             ,case when lead(timestamp) over(partition by session_id order by timestamp)-timestamp > '00:05:00.000' then 1 end as diff
       from   raw_data join labels using(session_id)
       ) t
where  diff = 1


session_id
timestamp
user_id
start_time
number_of_sessions_with_problem


656
2016-04-01 00:16:19.687
9
2016-04-01 00:03:39
2

657
2016-04-01 00:26:51.096
9
2016-04-01 00:26:51
2



SELECT 2

user_id	session_id	timestamp
8	652	2016-04-01 00:46:11.638
8	652	2016-04-01 00:47:00.566
8	652	2016-04-01 00:48:06.383
9	656	2016-04-01 00:14:17.707
9	656	2016-04-01 00:15:18.664
9	656	2016-04-01 00:16:19.687
9	656	2016-04-01 00:24:20.691
9	656	2016-04-01 00:25:23.681
9	657	2016-04-01 00:24:50.842
9	657	2016-04-01 00:26:51.096
9	657	2016-04-01 00:37:54.092

user_id	session_id	start_time	mode
48	652	2016-04-01 00:47:00	foot
9	656	2016-04-01 00:03:39	car
9	657	2016-04-01 00:26:51	car
9	658	2016-04-01 00:45:19	car
46	663	2016-04-01 00:13:12	car