with t as (
 select  1 id ,'aa@gmail.com' email, 1 status from dual
 union all select  2 ,'aa@gmail.com', 2 from dual
 union all select  3 ,'aa@gmail.com', 3 from dual
 union all select  4 ,'yy@gmail.com', 1 from dual
 union all select  5 ,'yy@gmail.com', 2 from dual
 union all select  6 ,'zz@gmail.com', 2 from dual
 union all select  7 ,'zz@gmail.com', 3 from dual
 union all select  8 ,'zz@gmail.com', 5 from dual
 union all select  9 ,'xx@gmail.com', 5 from dual
 union all select 10 ,'xy@gmail.com', 1 from dual
 union all select 11 ,'qx@gmail.com', 1 from dual
 union all select 12 ,'qx@gmail.com', 4 from dual
)
select  * from t where email  in (select email from t where status<=4 group by email having count(*)>=2 ) and status<=4
order by id