clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1305674 fiddles created (20246 in the last week).

create table tbl_excel_attendance( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(255) not null, verify_date datetime null, verify_state int not null, PRIMARY KEY (id) ); INSERT INTO tbl_excel_attendance (user_id,verify_date,verify_state) VALUES ('16','2019-11-08 07:46','0'), ('16','2019-11-07 18:05','1'), ('16','2019-11-07 12:52','0'), ('16','2019-11-07 07:35','0'), ('16','2019-11-06 19:45','1'), ('16','2019-11-06 07:39','0'), ('19','2019-11-08 07:00','0'), ('19','2019-11-07 18:04','1'), ('19','2019-11-07 12:51','0'), ('19','2019-11-07 06:42','0'), ('19','2019-11-05 18:28','1'), ('19','2019-11-05 06:59','0'), ('19','2019-11-05 06:57','0'), ('39','2019-11-07 19:09','1'), ('39','2019-11-07 12:37','0'), ('39','2019-11-07 11:59','0'), ('39','2019-11-07 09:29','0'), ('39','2019-11-07 07:39','0'), ('40','2019-11-07 06:39','1'), ('40','2019-11-07 06:38','0'), ('40','2019-11-06 20:39','0')
 hidden batch(es)


with cte1 as ( select *, coalesce(lag(verify_state) over (partition by user_id order by verify_date asc),1) prev_state, coalesce(lead(verify_state) over (partition by user_id order by verify_date asc),0) next_state from tbl_excel_attendance ), cte2 as ( select *, lead(verify_date) over (partition by user_id order by verify_date asc) next_date from cte1 where (prev_state,verify_state,next_state) in ( (1,0,0), (1,0,1), (0,1,0) ) ) select * from cte2 where verify_state = 0 and next_date is not null order by 2,3
id user_id verify_date verify_state prev_state next_state next_date
6 16 2019-11-06 07:39:00 0 1 1 2019-11-06 19:45:00
4 16 2019-11-07 07:35:00 0 1 0 2019-11-07 18:05:00
13 19 2019-11-05 06:57:00 0 1 0 2019-11-05 18:28:00
10 19 2019-11-07 06:42:00 0 1 0 2019-11-07 18:04:00
18 39 2019-11-07 07:39:00 0 1 0 2019-11-07 19:09:00
21 40 2019-11-06 20:39:00 0 1 0 2019-11-07 06:39:00
 hidden batch(es)