Average wait time last N days for M event

SET LINESIZE 230
SET PAGESIZE 9999
SET VERIFY   OFF
select
	   etime,
	   event_name,
       round((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),1) avg_ms
from (
select
       s.dbid,
       to_char(s.BEGIN_INTERVAL_TIME,'DD/MM/YY HH24:MI')  btime,
	   to_char(s.END_INTERVAL_TIME,'DD/MM/YY HH24:MI')  etime,
	   e.event_name,
       total_waits count_end,
       time_waited_micro/1000 time_ms_end,
       Lag (e.time_waited_micro/1000)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,
       Lag (e.total_waits)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg
from
       DBA_HIST_SYSTEM_EVENT e,
       DBA_HIST_SNAPSHOT s
where
       s.snap_id=e.snap_id
	   and s.begin_interval_time>=trunc(sysdate) - &days_back and s.end_interval_time <= sysdate
   and upper(e.event_name) like upper('%&wait_event_name%')
   and  s.dbid=e.dbid
)
where round((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),1) > 10
order by btime, event_name
/

Critical wait events

SET LINESIZE 230
SET PAGESIZE 9999
SET VERIFY   OFF
select
	   etime,
	   event_name,
       round((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),1) avg_ms
from (
select
       s.dbid,
       to_char(s.BEGIN_INTERVAL_TIME,'DD/MM/YY HH24:MI')  btime,
	   to_char(s.END_INTERVAL_TIME,'DD/MM/YY HH24:MI')  etime,
	   e.event_name,
       total_waits count_end,
       time_waited_micro/1000 time_ms_end,
       Lag (e.time_waited_micro/1000)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,
       Lag (e.total_waits)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg
from
       DBA_HIST_SYSTEM_EVENT e,
       DBA_HIST_SNAPSHOT s
where
       s.snap_id=e.snap_id
	   and s.begin_interval_time>=trunc(sysdate) - &days_back and s.end_interval_time <= sysdate
   and e.event_name in (
                  'log file sync',
                  'log file parallel write',
				  'buffer busy waits',
				  'db file sequential read',
				  'enq: TX - row lock contention',
				  'enq: SQ - contention',
				  'log file switch (checkpoint incomplete)',
				  'log file switch completion'
                )
   and  s.dbid=e.dbid
)
where round((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),1) > &avg_wait_ms
order by btime, event_name
/