Share price less than certain value for 6 consecutive business days or more in the past month

Question

This is for MySQL 8.X database.

My requirement is as follows: If a share price is less than a certain value for 6 consecutive business days or more in the past month then I need to inform the investor.

There will not be any share price on holidays and weekends. So there will not be any data for these days.

For example: In the below data, the share price is less than 100 for 6 consecutive business days between 14th and 21 Dec 2021. I need to find such shares and the number of days the share price has gone below the target value.

effective_date	security	market_price
1-Dec-2021	STOCKNAME	99
2-Dec-2021	STOCKNAME	98
3-Dec-2021	STOCKNAME	97
6-Dec-2021	STOCKNAME	101
7-Dec-2021	STOCKNAME	99
8-Dec-2021	STOCKNAME	98
9-Dec-2021	STOCKNAME	97
10-Dec-2021	STOCKNAME	96
13-Dec-2021	STOCKNAME	102
14-Dec-2021	STOCKNAME	99
15-Dec-2021	STOCKNAME	98
16-Dec-2021	STOCKNAME	97
17-Dec-2021	STOCKNAME	96
20-Dec-2021	STOCKNAME	95
21-Dec-2021	STOCKNAME	99
22-Dec-2021	STOCKNAME	102
23-Dec-2021	STOCKNAME	103
24-Dec-2021	STOCKNAME	114

Expected Output:

security    number_of_days
STOCKNAME   6

Answer 1

This is a gaps-and-island problem, with the islands being the consecutive days.

You need to assign a unique value to each consecutive group of rows that qualify (ie value < 100) and then count the rows in each group, selecting the max of these:

with g as (
    select *,
        Row_Number() over (partition by security order by effective_date)
        - Row_Number() over (partition by security, 
            case when market_price<100 then 1 else 0 end order by effective_date
        ) cnt
    from t
)
select security, Count(*) number_of_days
from g
group by security, cnt
order by number_of_days desc
limit 1

Example Fiddle