SQLize | PHPize | SQLtest

CREATE TABLE student ( id SERIAL PRIMARY KEY, State VARCHAR(255) NOT NULL ); INSERT INTO student(State) VALUES ('Suc'), ('Suc'), ('Fail'), ('Suc'), ('Suc'), ('Suc'), ('Fail'), ('Fail'), ('Suc'), ('Suc'), ('Fail'), ('Fail'), ('Fail'), ('Fail') ; with end_ as ( select * from ( SELECT id, State, case when lead(State) over(order by id) != State or lead (State) over(order by id) is null then 'end' else null end as tp from student ) a where tp is not null ), start_ as ( select s.* from student s join end_ e on e.id - s.id = 2 and e.State = s.State ) select s.* from student s join end_ e on e.id - s.id between 0 and 2 and e.State = s.State join start_ st on s.id - st.id between 0 and 2 and st.State = s.State