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 ),
('4', '2018-04-28', '2019-04-28', '2020-01-03' ),
('4', '2019-03-07', '2021-08-01', NULL ),
('4', '2020-02-22', '2021-02-22', NULL )
select t.*,
datediff(start, prev_cancelled) as num_days_since_cancel
from (select dt.*,
max(cancelled) over -- latest date per id
(partition by id
order by start
rows between unbounded preceding and 1 preceding) as prev_cancelled
from dt
) t
-- remove negative duration
where datediff(start, prev_cancelled) >= 0;
| 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 |
| 4 | 2020-02-22 00:00:00 | 2021-02-22 00:00:00 | null | 2020-01-03 00:00:00 | 50 |