Use SQL to find best 4 consecutive weeks-CodePudding

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)

CodePudding user response：

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:

enter image description here

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.

CodePudding user response：

you can make use of ROWS BETWEEN clause for this sort of problems.

Here is a way to get the desired output.

First sum up the values based on previous 3 rows and current row. Then rank them by using row_number() over() function and then, return rows and till 4 weeks from rnk=1

with ranked_weeks 
 as (
select x.*,row_number() over(partition by product order by sum_sales desc) as rnk 
  from (
select product
      ,weeknumber
      ,sales
      ,sum(sales) over(partition by product order by weeknumber rows between 3 preceding and current row) as sum_sales
  from t      
  )x
 )
 ,best_week
 as( 
   select product
          ,weeknumber-4 as low_val
          ,weeknumber as high_val
          ,sales
          ,sum_sales 
     from ranked_weeks rw
    where rw.rnk=1
    )
select t.product,t.weeknumber,t.sales,bw.sum_sales   
  from t
  join best_week bw
    on t.product=bw.product
   and t.weeknumber>bw.low_val
   and t.weeknumber<=bw.high_val
order by t.product,t.weeknumber,t.sales,bw.sum_sales 



 --------- ------------ ------- ----------- 
| product | weeknumber | sales | sum_sales |
 --------- ------------ ------- ----------- 
| A       |          1 |    17 |        64 |
| A       |          2 |    20 |        64 |
| A       |          3 |    17 |        64 |
| A       |          4 |    10 |        64 |
| S       |          5 |    24 |        74 |
| S       |          6 |    16 |        74 |
| S       |          7 |    10 |        74 |
| S       |          8 |    24 |        74 |
 --------- ------------ ------- -----------

link https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ba3c9f4ce4a5c69d6c67326b8be105c5