SQL to show which data hasn't changed in 8 minutes

set feedback off

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

create table foo_ (
  time_    date,
  volulme  number
);


insert into foo_ values ('01.01.2000 01:01:00', 12);
insert into foo_ values ('01.01.2000 01:02:00',  8);
insert into foo_ values ('01.01.2000 01:03:00', 14);
insert into foo_ values ('01.01.2000 01:04:00',  6);
insert into foo_ values ('01.01.2000 01:05:00', 24);
insert into foo_ values ('01.01.2000 01:06:00',  5);
insert into foo_ values ('01.01.2000 01:07:00', 29);
insert into foo_ values ('01.01.2000 01:08:00',  2);
insert into foo_ values ('01.01.2000 01:09:00', 37);
insert into foo_ values ('01.01.2000 01:10:00',  3);
insert into foo_ values ('01.01.2000 01:11:00',  3);
insert into foo_ values ('01.01.2000 01:12:00',  3);
insert into foo_ values ('01.01.2000 01:13:00',  3);
insert into foo_ values ('01.01.2000 01:14:00',  3);
insert into foo_ values ('01.01.2000 01:15:00',  3);
insert into foo_ values ('01.01.2000 01:16:00',  3);
insert into foo_ values ('01.01.2000 01:17:00',  3);
insert into foo_ values ('01.01.2000 01:18:00', 18);
insert into foo_ values ('01.01.2000 01:19:00',  7);
insert into foo_ values ('01.01.2000 01:20:00',  7);
insert into foo_ values ('01.01.2000 01:21:00',  7);
insert into foo_ values ('01.01.2000 01:22:00',  7);
insert into foo_ values ('01.01.2000 01:23:00',  7);
insert into foo_ values ('01.01.2000 01:24:00',  7);
insert into foo_ values ('01.01.2000 01:25:00',  7);
insert into foo_ values ('01.01.2000 01:26:00', 11);

@dp

explain plan for
-- fourth select:
-- The interesting rows are those that have
-- a difference of at least 8 between the 
-- previous row and the current row:
select
  time_
from (
  -- third select:
  --   Find the difference between previous and
  --   current time_rank's
  select
    lead(time_rank,1,0) over (order by time_) - time_rank diff,
    time_
  from (
    -- second select:
    --   Filter those rows that have a null time_rank
select
  time_,
  time_rank,
  volulme
from (
  -- innermost select:
  --   Find Adjacent volulmes: rows that have the
  --   same volulme as the previous one (ordered by time)
  --   return a null value for time_rank; otherwise
  --   the get the rank (ordered by time) of the row in
  --   question
      select
        time_,
        case 
          when lag(volulme,1) over (order by time_) = volulme then null
          else rank() over(order by time_) end time_rank,
          volulme
         from
           foo_
   )
    where
      time_rank is not null)
    )
where 
  diff >=8;

@ep