How to get maximum of Five consecutive values in an Year?

I have a yearly list of sales of an item for the past 20 years.

the data is like this.

date       ;     sales value
2001-01-01 ;      423
2001-01-02 ;      152
2001-01-03 ;      162
2001-01-04 ;      172
.
.
.

I have a behavioral problem. I must find the five consecutive days where the sum of sales is maximum in a year, for each year, for the past 20 years. Then using the result i must analyse the spending pattern.

how can i get the 5 consecutive days whose sum is maximum in a year?

I must get it for all years with dates and sum of sales in those 5 days total value. Can anyone help me in my assignment, please?

TIA

Answer

Well, in MySQL 8+, you can use window functions. In earlier versions, a correlated subquery. That looks like:

select year(t.date),
       max(next_5_days_sales),
       substring_index(group_concat(date order by next_5_days_sales desc), ',', 1) as date_at_max
from (select t.*,
             (select sum(t2.sales)
              from t t2
              where t2.date >= t.date and t2.date < t.date + interval 5 day
             ) as next_5_days_sales
      from t
     ) t
group by year(t.date);

Notes:

• You will need to reset the group_concat_max_len, because 1024 is probably not long enough for the intermediate result.
• This allows the periods to span year boundaries.

In MySQL 8, use window functions!

select t.*
from (select t.*,
             row_number() over (partition by year(date) order by next_5_days_sales) as seqnum
      from (select t.*,
                   sum(t2.sales) over (order by date range between current row and 4 following) as as next_5_days_sales
            from t
           ) t
      ) t
where seqnum = 1;