-- (c) Kyle Hailey 2007

break on  start_time
clear breaks
column f_secs new_value v_secs
column f_mins new_value v_mins
select 900 f_secs from dual;
select &v_secs/60 f_mins from dual;

select * from (
   select
        to_char(trunc(sample_time,'HH')+
             (trunc((sample_time-trunc(sample_time,'HH'))*24/(&v_mins/60))+1)/(60/&v_mins)/24 ,'MON-DD HH:MI') start_time,
        count(*),
        sql_id,
        fixed_table_sequence,
        rank()
          over (
            partition by
                    trunc(sample_time,'HH')+
             (trunc((sample_time-trunc(sample_time,'HH'))*24/(&v_mins/60))+1)/(60/&v_mins)/24
            order by count(*) desc ) topn
   from sash
   where
          sql_id > 0
     and  sql_id is not null
     and  sample_time > sysdate - (30)/(24*60)
   group by
            sql_id,
            fixed_table_sequence,
            trunc(sample_time,'HH')+
            (trunc((sample_time-trunc(sample_time,'HH'))*24/(&v_mins/60))+1)/(60/&v_mins)/24
)
where  topn < 3
/
clear breaks