By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `dt` (
`id` int(6) unsigned NOT NULL,
`start` TIMESTAMP,
`end` TIMESTAMP,
`cancelled` TIMESTAMP NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `dt` (`id`, `start`, `end`, `cancelled`) VALUES
('1', '2020-01-01', '2020-12-31', '2021-01-10'),
('1', '2021-02-01', '2022-01-31', NULL ),
('2', '2021-01-01', '2020-12-31', NULL ),
('3', '2020-01-01', '2020-06-30', '2020-07-01'),
('3', '2020-07-10', '2021-01-09', '2021-01-31'),
('3', '2021-02-02', '2021-08-01', NULL )
select t.*,
datediff(start, prev_cancelled) as num_days_since_cancel
from (select dt.*,
lag(cancelled) over (partition by id order by start) as prev_cancelled
from dt
) t
where prev_cancelled is not null;
| id | start | end | cancelled | prev_cancelled | num_days_since_cancel |
|---|---|---|---|---|---|
| 1 | 2021-02-01 00:00:00 | 2022-01-31 00:00:00 | null | 2021-01-10 00:00:00 | 22 |
| 3 | 2020-07-10 00:00:00 | 2021-01-09 00:00:00 | 2021-01-31 00:00:00 | 2020-07-01 00:00:00 | 9 |
| 3 | 2021-02-02 00:00:00 | 2021-08-01 00:00:00 | null | 2021-01-31 00:00:00 | 2 |