Skip to content
Advertisement

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

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

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement