Consecutive n week calculation-CodePudding

I have a table consisting of 3 columns: Product, Week, Units Sold. I am trying to come up with a table to show the best consecutive 4 week sales.

I have tried several different window analytical functions and can’t seem to get what I need.

This is the input.


PRODUCT WEEK SALES 
ICE     1   17
ICE     2   20
ICE     3   17
ICE     4   10
ICE     5   12
ICE     6   13
ICE     7    2
ICE     8   25
WATER   1   25
WATER   2   20
WATER   3   9
WATER   4   7
WATER   5   24
WATER   6   16
WATER   7   10
WATER   8   16
SODA    1   22
SODA    2   2
SODA    3   10
SODA    4   24
SODA    5   9
SODA    6   20
SODA    7   9
SODA    8   21

This is the output.

PRODUCT BEST_4_WK   BEST_4_WK_SALE
ICE     1-4            64
WATER   5-8            66
SODA    3-6            63

I think I need to use LAG() (for summing the sales), MIN(), MAX() casting as string concatenation (for the weeks). I tried for hours and couldn’t get it. Thank you for your help!

CodePudding user response：

Try this

with cte as
(
select 'ICE' as product,     1 as week,   17 as sales union all
select 'ICE' as product,     2 as week,   20 as sales union all
select 'ICE' as product,     3 as week,   17 as sales union all
select 'ICE' as product,     4 as week,   10 as sales union all
select 'ICE' as product,     5 as week,   12 as sales union all
select 'ICE' as product,     6 as week,   13 as sales union all
select 'ICE' as product,     7 as week,    2 as sales union all
select 'ICE' as product,     8 as week,   25 as sales union all
select 'WATER' as product,   1 as week,   25 as sales union all
select 'WATER' as product,   2 as week,   20 as sales union all
select 'WATER' as product,   3 as week,   9  as sales union all
select 'WATER' as product,   4 as week,   7  as sales union all
select 'WATER' as product,   5 as week,   24 as sales union all
select 'WATER' as product,   6 as week,   16 as sales union all
select 'WATER' as product,   7 as week,   10 as sales union all
select 'WATER' as product,   8 as week,   16 as sales union all
select 'SODA' as product,    1 as week,   22 as sales union all
select 'SODA' as product,    2 as week,   2  as sales union all
select 'SODA' as product,    3 as week,   10 as sales union all
select 'SODA' as product,    4 as week,   24 as sales union all
select 'SODA' as product,    5 as week,   9  as sales union all
select 'SODA' as product,    6 as week,   20 as sales union all
select 'SODA' as product,    7 as week,   9  as sales union all
select 'SODA' as product,    8 as week,   21 as sales
),cte2 as
(
select  *, 
        sum(sales) over ( partition by product order by week rows between current row and 3 following ) as summed
from cte
)
select  product,
        cast(week as string)||'-'||cast(week   3 as string) as week,
        summed
from cte2
where 1 = 1
qualify row_number() over (partition by product order by summed desc)  = 1