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.

Expected Output:

security    number_of_days
STOCKNAME   6

CodePudding user response：

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