id INT NOT NULL,

user_id INT NULL,

activity VARCHAR(45),

log_time DATETIME NOT NULL DEFAULT NOW(),

PRIMARY KEY(id))

ENGINE = InnoDB;

insert into activities

(id, user_id, activity, log_time)

(6,1,'start', '2021-10-12 10:00:00'),

(2,1,'task' , '2021-10-12 10:10:00'),

(7,1,'task' , '2021-10-12 10:40:00'),

(3,1,'start', '2021-10-12 16:00:00'),

(1,1,'task', '2021-10-12 16:10:00'),

(9,1,'start', '2021-10-14 08:00:00'),

(10,1,'start','2021-10-16 09:00:00'),

(4,1,'task', '2021-10-16 09:10:00'),

(8,2,'start', '2021-10-12 10:00:00'),

(5,2,'task', '2021-10-12 10:20:00');

(

user_id, partition_condition ,TIMESTAMPDIFF(minute,min(log_time),max(log_time))time_diff

FROM (

SELECT

id, user_id, activity, log_time,

row_number()over (Partition by user_id order by log_time)rn,

sum(case when activity='start' then 1 else 0 end) over (partition by user_id order by log_time) as partition_condition

FROM activities

) as tasks

group by user_id, partition_condition

)

select user_id,sum(time_diff)total_time from tasks

group by user_id