select  case when (product = 'HAMMER') and (weeknumber between 1 and 4) then
             'HAMMER'
             when (product = 'SINK') and (weeknumber between 5 and 8) then
             'SINK'
             when (product = 'BUCKET') and (weeknumber between 3 and 6) then
             'BUCKET'
        end product,
        case when (product = 'HAMMER') and (weeknumber between 1 and 4) then
             '1-4'
             when (product = 'SINK') and (weeknumber between 5 and 8) then
             '5-8'
             when (product = 'BUCKET') and (weeknumber between 3 and 6) then
             '3-6'
        end best_consec_4weeks,
        sum(sales)
from    test_tab
group by case when (product = 'HAMMER') and (weeknumber between 1 and 4) then
             'HAMMER'
             when (product = 'SINK') and (weeknumber between 5 and 8) then
             'SINK'
             when (product = 'BUCKET') and (weeknumber between 3 and 6) then
             'BUCKET'
        end,
        case when (product = 'HAMMER') and (weeknumber between 1 and 4) then
             '1-4'
             when (product = 'SINK') and (weeknumber between 5 and 8) then
             '5-8'
             when (product = 'BUCKET') and (weeknumber between 3 and 6) then
             '3-6'
        end

drop table table1 purge
/
create table table1
  as
    select  'HAMMER' product,1 weeknumber,17 sales from dual union all
    select  'HAMMER',2,20 from dual union all
    select  'HAMMER',3,17 from dual union all
    select  'HAMMER',4,10 from dual union all
    select  'HAMMER',5,12 from dual union all
    select  'HAMMER',6,13 from dual union all
    select  'HAMMER',7,2  from dual union all
    select  'HAMMER',8,25 from dual union all
    select  'SINK',1,25 from dual union all
    select  'SINK',2,20 from dual union all
    select  'SINK',3,9  from dual union all
    select  'SINK',4,7  from dual union all
    select  'SINK',5,24 from dual union all
    select  'SINK',6,16 from dual union all
    select  'SINK',7,10 from dual union all
    select  'SINK',8,16 from dual union all
    select  'BUCKET',1,22 from dual union all
    select  'BUCKET',2,2  from dual union all
    select  'BUCKET',3,10 from dual union all
    select  'BUCKET',4,24 from dual union all
    select  'BUCKET',5,9  from dual union all
    select  'BUCKET',6,20 from dual union all
    select  'BUCKET',7,9  from dual union all
    select  'BUCKET',8,21 from dual
/
with t1 as (
            select  product,
                    weeknumber,
                    sum(sales) over(
                                    partition by product
                                    order by weeknumber
                                    range between current row and 3 following
                                   ) consec_4week_sales
              from  table1
           ),
     t2 as (
            select  product,
                    weeknumber,
                    consec_4week_sales,
                    dense_rank() over(
                                      partition by product
                                      order by consec_4week_sales desc
                                     ) consec_4week_sales_drnk
              from  t1
           )
select  product,
        weeknumber || '-' || (weeknumber + 3) best_consec_4weeks,
        consec_4week_sales best_consec_4weeks_sls
  from  t2
  where consec_4week_sales_drnk = 1
/

PRODUC BEST_CONSEC_4WEEKS BEST_CONSEC_4WEEKS_SLS
------ ------------------ ----------------------
BUCKET 3-6                                    63
HAMMER 1-4                                    64
SINK   5-8                                    66

SQL>