Okay, I'm new to the whole analytics thing... but getting my feet wet.

Here's the complex problem. I have a table that has date, table_name, and % changes in the table for that day (gathered from dba_tab_modifications each day). I'll post the sql down below.

What I want to know is which tables will hit the 10% (.10) mark... and on what day. Here's my issue. Assume the table does 8% on day 1, 2, 3 & 4 . I want the report to say:
Code:
TABLE_OWNER TABLE_NAME     MONCHG     TUECHG     WEDCHG    THURCHG
----------- ---------- ---------- ---------- ---------- ----------
SCOTT       DEPT                         .16                   .16
SCOTT       EMP              1.94       1.94       1.94       1.94
Here's what the report says now:
Code:
 
TABLE_OWNER TABLE_NAME     MONCHG     TUECHG     WEDCHG    THURCHG
----------- ---------- ---------- ---------- ---------- ----------
SCOTT       DEPT                         .16        .24        .32
SCOTT       EMP              1.94       1.94       1.94       1.94

I have it doing the cummulative calculation now, but I don't know how to get it to "reset" the cummulative counter to 0 after I hit 10%.

Here's my code:
To create the table:
Code:
create table our_tab_modifications (
 dt                     date,
 TABLE_OWNER            VARCHAR2(30),
 TABLE_NAME             VARCHAR2(30),
 DELTA			NUMBER)
;

insert into our_tab_modifications  values (to_date('2007-04-02','YYYY-MM-DD'),'SCOTT','EMP',1.94);
insert into our_tab_modifications  values (to_date('2007-04-03','YYYY-MM-DD'),'SCOTT','EMP',1.94);
insert into our_tab_modifications  values (to_date('2007-04-04','YYYY-MM-DD'),'SCOTT','EMP',1.94);
insert into our_tab_modifications  values (to_date('2007-04-05','YYYY-MM-DD'),'SCOTT','EMP',1.94);
insert into our_tab_modifications  values (to_date('2007-04-02','YYYY-MM-DD'),'SCOTT','DEPT',0.08);
insert into our_tab_modifications  values (to_date('2007-04-03','YYYY-MM-DD'),'SCOTT','DEPT',0.08);
insert into our_tab_modifications  values (to_date('2007-04-04','YYYY-MM-DD'),'SCOTT','DEPT',0.08);
insert into our_tab_modifications  values (to_date('2007-04-05','YYYY-MM-DD'),'SCOTT','DEPT',0.08);
Intermediate Query:
Code:
with deltas as (
  select  dt, table_owner, table_name,
          trim(to_char(dt, 'Day')) Weekday,
          delta
  from    our_tab_modifications
  where   dt > trunc(sysdate) - 5
    and   delta > 0
  )
select     dt, table_owner, table_name, weekday, delta,
           sum(delta) over (partition by table_owner, table_name
                             order by dt RANGE UNBOUNDED PRECEDING) Cummulative
from    deltas
;

DT        TABLE_OWNE TABLE_NAME WEEKDAY        DELTA CUMMULATIVE
--------- ---------- ---------- --------- ---------- -----------
02-APR-07 SCOTT      DEPT       Monday           .08         .08
03-APR-07 SCOTT      DEPT       Tuesday          .08         .16
04-APR-07 SCOTT      DEPT       Wednesday        .08         .24
05-APR-07 SCOTT      DEPT       Thursday         .08         .32
02-APR-07 SCOTT      EMP        Monday          1.94        1.94
03-APR-07 SCOTT      EMP        Tuesday         1.94        3.88
04-APR-07 SCOTT      EMP        Wednesday       1.94        5.82
05-APR-07 SCOTT      EMP        Thursday        1.94        7.76

8 rows selected.
Final Query
Code:
with deltas as (
  select  dt, table_owner, table_name,
          trim(to_char(dt, 'Day')) Weekday,
          delta
  from    our_tab_modifications
  where   dt > trunc(sysdate) - 5
    and   delta > 0
  )
,
mid_point  as (
select     dt, table_owner, table_name, weekday, delta,
           sum(delta) over (partition by table_owner, table_name
                            order by dt RANGE UNBOUNDED PRECEDING) Cummulative
from    deltas
)
,
summary as (
select  table_owner, table_name, weekday,
        case when delta > .1 then delta
             when Cummulative > .1 then cummulative
             else null
        end change
from mid_point
where cummulative > .1
)
select  table_owner, table_name,
	sum(case when Weekday = 'Monday' then change else null end) MonChg,
	sum(case when Weekday = 'Tuesday' then change else null end) TueChg,
	sum(case when Weekday = 'Wednesday' then change else null end) WedChg,
	sum(case when Weekday = 'Thursday' then change else null end) ThurChg
from summary
group by table_owner, table_name
order by table_owner, table_name


TABLE_OWNER TABLE_NAME     MONCHG     TUECHG     WEDCHG    THURCHG
----------- ---------- ---------- ---------- ---------- ----------
SCOTT       DEPT                         .16        .24        .32
SCOTT       EMP              1.94       1.94       1.94       1.94
Again, what I want:
Code:
TABLE_OWNER TABLE_NAME     MONCHG     TUECHG     WEDCHG    THURCHG
----------- ---------- ---------- ---------- ---------- ----------
SCOTT       DEPT                         .16                   .16
SCOTT       EMP              1.94       1.94       1.94       1.94
I'm sure some analytic function will help here... just not sure what.

Any help is appreciated!

Thanks!
Jodie