overlapping-intervals.sql

SQL> with s as (
    select 1 a,  10 b from dual union all
    select 20 a, 30 b from dual union all
    select 30 a, 40 b from dual union all
    select 50 a, 60 b from dual union all
    select 55 a, 57 b from dual union all
    select 65 a, 75 b from dual union all
    select 70 a, 80 b from dual union all
    select 75 a, 85 b from dual
)
, q as (
select s.a, s.b,
       case when w.a is not null then null
            when l.a is not null then null
            else s.a
        end a_min,
       case when w.a is not null then null
        when r.a is not null then null
        else s.b
        end b_max
  from s
  left join s l on s.a between l.a and l.b and not (l.a = s.a and l.b = s.b)
  left join s r on s.b between r.a and r.b and not (r.a = s.a and r.b = s.b)
  left join s w on w.a < s.a and w.b > s.b and not (r.a = s.a and r.b = s.b)
)
, qq  as (select distinct a_min, b_max from q where not (a_min is null and b_max is null))
, qqq as (select distinct
        nvl(a_min, (select max(a_min)
                      from qq q1
                     where q1.b_max is null
                       and q1.a_min < qq.b_max
                   )) a_min
     , nvl(b_max, (select min(b_max)
                     from qq q1
                    where q1.a_min is null
                      and q1.b_max > qq.a_min
                   )) b_max
   from qq
)
select sum(b_max-a_min) len from qqq
;

       LEN
----------
        59