Use SQL to find best 4 consecutive weeks

I have a dataset that has a product, weeknumber, and sales for that week.

I am trying to find the 4 best consecutive sales weeks in the data, so for example, Product A’s 4 best weeks are 1-4 and Product S’s best weeks are 5-8

I am using SQL to query the table to return the 4 best consecutive weeks & the totals during those weeks (grouping by product)

I am struggling on how to tackle this as I am not an expert in SQL.

So far, I have tried using a lag + subquery to create lags at intervals 1,2,3, & 4. My thought is to do a running total for each new lag column & then find the max value.

I created the lag tables, but something is happening in my ‘over’ statement that is reordering the output for the lags.

I have no idea if this is a good way to tackle the problem, but I have researched and not found anything that can help me for this problem.

select PRODUCT, WEEKNUMBER, LAG_SALES1, LAG_SALES2,
        SUM(LAG_SALES1) OVER (ORDER BY WEEKNUMBER) AS RUNNING_TOTAL1,
        SUM(LAG_SALES2) OVER (ORDER BY WEEKNUMBER) AS RUNNING_TOTAL2,
        SUM(LAG_SALES3) OVER (ORDER BY WEEKNUMBER) AS RUNNING_TOTAL3,

FROM(
    SELECT PRODUCT, WEEKNUMBER, SALES,
        LAG(SUM(SALES),1) OVER( ORDER BY PRODUCT, WEEKNUMBER) AS LAG_SALES1, LAG(SUM(SALES),2) OVER( ORDER BY PRODUCT, WEEKNUMBER) AS LAG_SALES2, 
        LAG(SUM(SALES),3) OVER( ORDER BY PRODUCT, WEEKNUMBER) AS LAG_SALES3, 
        SUM(SALES) OVER (ORDER BY WEEKNUMBER) AS RUNNING_TOTAL0,
     
   
    from t 
    GROUP BY PRODUCT, WEEKNUMBER, SALES
    ORDER BY PRODUCT, WEEKNUMBER
)
WHERE PRODUCT = 'A'
GROUP BY PRODUCT, WEEKNUMBER, LAG_SALES1, LAG_SALES2, LAG_SALES3
ORDER BY PRODUCT, WEEKNUMBER

CREATE TABLE t (
    PRODUCT varchar(255),
    WEEKNUMBER int,
    SALES int);

INSERT INTO t(PRODUCT, WEEKNUMBER, SALES)
VALUES
('A',1,17),
('A',2,20),
('A',3,17),
('A',4,10),
('A',5,12),
('A',6,13),
('A',7,2),
('A',8,25),
('S',1,25),
('S',2,20),
('S',3,9),
('S',4,7),
('S',5,24),
('S',6,16),
('S',7,10),
('S',8,24)

Answer

I provide you a sample sql on MySQL. You can use it in other sql languages simply with change CONCAT function.

SELECT t1.PRODUCT,CONCAT(t1.WEEKNUMBER,",",t2.WEEKNUMBER,",",t3.WEEKNUMBER,",",t4.WEEKNUMBER) as weeks, t1.SALES+t2.SALES+t3.SALES+t4.SALES as sumSale FROM `t` as t1
inner join `t` as t2
inner join `t` as t3
inner join `t` as t4
where t1.PRODUCT = t2.PRODUCT and t1.PRODUCT = t3.PRODUCT and t1.PRODUCT = t4.PRODUCT
and t1.WEEKNUMBER = t2.WEEKNUMBER+1
and t1.WEEKNUMBER = t3.WEEKNUMBER+2
and t1.WEEKNUMBER = t4.WEEKNUMBER+3;

Result will be:

And you can find max of sumSale with corresponding weeks.

I provide you the MS SQL sample below which using temp table to do more query:

SELECT t1.PRODUCT, 
CONCAT(t1.WEEKNUMBER,',',t2.WEEKNUMBER,',',t3.WEEKNUMBER,',',t4.WEEKNUMBER) as weeks, 
t1.SALES+t2.SALES+t3.SALES+t4.SALES as sumSale
into #tempTable 
FROM t as t1, t as t2, t as t3, t as t4
WHERE 
t1.PRODUCT = t2.PRODUCT and t1.PRODUCT = t3.PRODUCT and t1.PRODUCT = t4.PRODUCT
and t1.WEEKNUMBER = t2.WEEKNUMBER+1
and t1.WEEKNUMBER = t3.WEEKNUMBER+2
and t1.WEEKNUMBER = t4.WEEKNUMBER+3

SELECT PRODUCT,MAX(sumSale) as msale 
INTO #temp2 
FROM #tempTable 
GROUP BY PRODUCT

SELECT tt1.* FROM #tempTable as tt1
INNER JOIN #temp2 as tt2
ON tt1.PRODUCT = tt2.PRODUCT
WHERE tt1.sumSale = tt2.msale

Result will be as:

In Oracle you can use simply IN clause instead of JOIN for last section.