Looking for an ADF solution or a SQL query/SP (or maybe even Databricks if necessary): I'm looking to find the sum of peak(s) of given values by type over a time period. Furthermore, if the peak doesn't have a downward trend during the given time period, it should not be counted.
The data, for example:
| Type | Amount | Date | ||
| AAA | 10 | 2021-07-11 | ||
| AAA | 15 | 2021-07-11 | ||
| AAA | 20 | 2021-07-11 | ||
| AAA | 25 | 2021-07-11 *Need this peak | ||
| AAA | 20 | 2021-07-11 | ||
| AAA | 15 | 2021-07-11 | ||
| AAA | 10 | 2021-07-11 | ||
| AAA | 15 | 2021-07-11 | ||
| AAA | 20 | 2021-07-11 | ||
| AAA | 25 | 2021-07-11 | ||
| AAA | 30 | 2021-07-11 *Need this peak added to previous peak | ||
| AAA | 25 | 2021-07-11 | ||
| AAA | 20 | 2021-07-11 | ||
| AAA | 15 | 2021-07-11 | ||
| AAA | 10 | 2021-07-11 | ||
| BBB | 10 | 2021-07-11 | ||
| BBB | 20 | 2021-07-11 | ||
| BBB | 30 | 2021-07-11 *Need this peak | ||
| BBB | 20 | 2021-07-11 | ||
| BBB | 10 | 2021-07-11 | ||
| CCC | 10 | 2021-07-11 | ||
| CCC | 20 | 2021-07-11 | ||
| CCC | 30 | 2021-07-11 | ||
| CCC | 20 | 2021-07-11 | ||
| CCC | 10 | 2021-07-11 | ||
| CCC | 20 | 2021-07-11 | ||
| CCC | 30 | 2021-07-11 | ||
| CCC | 40 | 2021-07-11 *This peak won't count because the downtrend happens the next day | ||
| CCC | 30 | 2021-07-12 | ||
| CCC | 20 | 2021-07-12 | ||
| CCC | 10 | 2021-07-12 |
The results for given day 2021-07-11 should be:
| Type | Total | ||
| AAA | 55 | ||
| BBB | 30 | ||
| (CCC not included since there was no downtrend for the given date) |
I'm not even really sure where to start except for maybe getting the value of each row that has a following row that is less (starts downtrending).