-- CREATE DATES
create table dates as 
      select t.SYSTEMVALIDSINCE as snap_date
      from advert_fact t group by t.systemvalidsince
      order by 1
;
merge into dates t using (
      select t.SYSTEMVALIDSINCE as snap_date
      from advert_fact t group by t.systemvalidsince
      order by 1
     ) s on (t.snap_date = s.snap_date)
     when not matched then insert (t.snap_date) values (s.snap_date)    
;
--CREATE TIME
--drop table time;
create table time as
select rownum as rn, snap_date, lead(snap_date) over (order by snap_date) as next_date, lag(snap_date) over (order by snap_date) as prev_date  
from ( select * from dates order by 1)
; 
-- CREATE ADVERT
create table advert as select rownum as id, t.* from (
select systemid, min(systemvalidsince) as first_date, max(systemvalidsince) as last_date, count(*) tot_advert_cnt from advert_fact group by systemid) t;

ALTER TABLE advert
  ADD (
    CONSTRAINT advert_pk PRIMARY KEY (id)
  );