ТЕМА:

with src as (
  select 'Smith' username,'SUCCESS' log_result,to_date('15.12.10','dd.mm.yy') log_time from dual union all
  select 'Hacker','FAILURE',to_date('14.12.10','dd.mm.yy') from dual union all
  select 'Hacker','FAILURE',to_date('13.12.10','dd.mm.yy') from dual union all
  select 'Hacker','FAILURE',to_date('12.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','SUCCESS',to_date('11.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','FAILURE',to_date('10.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','SUCCESS',to_date('09.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','SUCCESS',to_date('07.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','FAILURE',to_date('06.12.10','dd.mm.yy') from dual union all
  select 'Hacker2','FAILURE',to_date('05.12.10','dd.mm.yy') from dual)
select 
  username
 from 
  src att1 inner join src att2
  on att1.username=att2.username
  and att1.log_result=att2.log_result
  and att1.log_time<att2.log_time
  inner join src att3
  on att1.username=att3.username
  and att1.log_result=att3.log_result
  and att3.log_time<att1.log_time
 where
  att1.log_result = 'FAILURE'
  and not exists
  (select 
     * 
    from
     src tmp
    where 
     tmp.username=att1.username and
     tmp.log_result<>att1.log_result and
     (tmp.log_time between att1.log_time and att2.log_time or
      tmp.log_time between att3.log_time and att1.log_time))                     
 group by 
  username;