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

Question

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

What have you tried so far, and what happened? You will learn your assignment better if we can help you find what went wrong, rather than just give the solution. — Meg, Feb 07 '19 at 21:24
Similar questions have been asked here: https://dba.stackexchange.com/questions/72257/how-to-find-n-consecutive-numbers-in-mysql and https://stackoverflow.com/questions/44150410/how-to-get-consecutive-numbers-using-mysql — Maciej Los, Feb 07 '19 at 21:29

Gordon Linoff · Accepted Answer · 2019-02-07T21:55:37.373

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;

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

1 Answers1