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