By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `test`
(
`date_time` DATETIME UNIQUE NOT NULL,
`data` INT NOT NULL
)
ENGINE InnoDB;
INSERT INTO `test` VALUES
('2017-01-02', 2),
('2017-01-03', 4),
('2017-01-04', 1),
('2017-01-05', 3),
('2017-01-06', 1),
('2017-01-07', 4),
('2017-01-08', 5),
('2017-01-09', -2),
('2017-01-10', 0),
('2017-01-11', -5),
('2017-01-12', 6),
('2017-01-13', 4),
('2017-01-14', 6)
;
SELECT DATE(`date_time`) AS `date`,
`data`,
MAX(`data`) OVER (ORDER BY `date_time` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `data_max`
FROM `test`
| date | data | data_max |
|---|---|---|
| 2017-01-02 | 2 | 2 |
| 2017-01-03 | 4 | 4 |
| 2017-01-04 | 1 | 4 |
| 2017-01-05 | 3 | 4 |
| 2017-01-06 | 1 | 4 |
| 2017-01-07 | 4 | 4 |
| 2017-01-08 | 5 | 5 |
| 2017-01-09 | -2 | 5 |
| 2017-01-10 | 0 | 5 |
| 2017-01-11 | -5 | 5 |
| 2017-01-12 | 6 | 6 |
| 2017-01-13 | 4 | 6 |
| 2017-01-14 | 6 | 6 |
WITH cte AS (SELECT DATE(`date_time`) AS `date`,
`data`,
MAX(`data`) OVER (ORDER BY `date_time` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `data_max`
FROM `test`),
cte2 AS (SELECT `date`,
`data`,
`data_max`,
CASE WHEN `data` < `data_max` THEN `data` - `data_max` END AS `data_diff`
FROM cte)
SELECT `data_max`,
MIN(CASE WHEN `data_diff` IS NOT NULL THEN date END) AS diff_date_from,
MAX(CASE WHEN `data_diff` IS NOT NULL THEN date END) AS diff_date_to
FROM cte2
GROUP BY `data_max`
HAVING diff_date_from IS NOT NULL
| data_max | diff_date_from | diff_date_to |
|---|---|---|
| 4 | 2017-01-04 | 2017-01-06 |
| 5 | 2017-01-09 | 2017-01-11 |
| 6 | 2017-01-13 | 2017-01-13 |