By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (
`cycle` INTEGER,
`date` DATE
);
INSERT INTO table1
(`cycle`, `date`)
VALUES
('1', '2021-01-15'),
('2', '2021-08-01'),
('3', '2021-09-08'),
('4', '2021-12-15');
Records: 4 Duplicates: 0 Warnings: 0
SELECT *
FROM (
SELECT *,DATE_SUB(LEAD(`date`) OVER(ORDER BY cycle), INTERVAL 1 DAY) endDate
FROM table1
) t1
WHERE endDate IS NOT NULL
| cycle | date | endDate |
|---|---|---|
| 1 | 2021-01-15 | 2021-07-31 |
| 2 | 2021-08-01 | 2021-09-07 |
| 3 | 2021-09-08 | 2021-12-14 |