MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `dt` (
  `msid` int(6) unsigned NOT NULL,
  `id` int(6) unsigned NOT NULL,
  `start` TIMESTAMP,
  `cancelled` TIMESTAMP NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `dt` (`msid`, `id`, `start`, `cancelled`) VALUES
  ('1', '1', '2020-01-01 09:00:00', NULL),
  ('2', '2', '2020-01-01 09:00:00', '2020-12-31 09:00:00' ),
  ('3', '2', '2021-01-01 09:00:00', NULL ),
  ('4', '3', '2020-01-01 09:00:00', '2020-06-30 09:00:00'),
  ('5', '3', '2020-02-01 09:00:00', '2020-06-30 09:00:00'),
  ('6', '3', '2020-07-01 09:00:00', NULL ) ;
  
  CREATE TABLE IF NOT EXISTS `periods` (
  `period_start` TIMESTAMP NOT NULL,
  `period_end` TIMESTAMP NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `periods` (`period_start`,`period_end`) VALUES
('2020-01-01 00:00:00', '2020-01-01 23:59:59'),
('2020-01-01 00:00:00', '2020-06-30 23:59:59'),
('2020-01-01 00:00:00', '2020-07-01 23:59:59'),
('2020-01-01 00:00:00', '2020-12-31 23:59:59'),
('2020-01-01 00:00:00', '2021-01-01 23:59:59'),
('2020-07-01 00:00:00', '2021-01-01 23:59:59'),
('2021-01-01 00:00:00', '2021-01-01 23:59:59');
SELECT * from dt;
SELECT * from periods;


msid
id
start
cancelled


1
1
2020-01-01 09:00:00
null

2
2
2020-01-01 09:00:00
2020-12-31 09:00:00

3
2
2021-01-01 09:00:00
null

4
3
2020-01-01 09:00:00
2020-06-30 09:00:00

5
3
2020-02-01 09:00:00
2020-06-30 09:00:00

6
3
2020-07-01 09:00:00
null




period_start
period_end


2020-01-01 00:00:00
2020-01-01 23:59:59

2020-01-01 00:00:00
2020-06-30 23:59:59

2020-01-01 00:00:00
2020-07-01 23:59:59

2020-01-01 00:00:00
2020-12-31 23:59:59

2020-01-01 00:00:00
2021-01-01 23:59:59

2020-07-01 00:00:00
2021-01-01 23:59:59

2021-01-01 00:00:00
2021-01-01 23:59:59


SELECT DISTINCT id, min(start) over (partition by id) as start FROM dt


id
start


1
2020-01-01 09:00:00

2
2020-01-01 09:00:00

3
2020-01-01 09:00:00


SELECT DISTINCT id, cancelled FROM dt WHERE cancelled IS NOT NULL


id
cancelled


2
2020-12-31 09:00:00

3
2020-06-30 09:00:00


WITH 
  tab_cancelled as (SELECT DISTINCT id, cancelled FROM dt WHERE cancelled IS NOT NULL)
SELECT DISTINCT dt.id, dt.start as rejoined 
FROM dt 
INNER JOIN tab_cancelled tc
ON tc.id = dt.id 
AND dt.start > tc.cancelled  


id
rejoined


2
2021-01-01 09:00:00

3
2020-07-01 09:00:00


SELECT DISTINCT period_start, 
                   period_end, 
                   count(dt1.id) over (partition by period_start, period_end) as new
     FROM periods
LEFT JOIN (SELECT id, MIN(start) as start FROM (select * from dt order by start) as t group by id) as dt1
       ON dt1.start between period_start and period_end


period_start
period_end
new


2020-01-01 00:00:00
2020-01-01 23:59:59
3

2020-01-01 00:00:00
2020-06-30 23:59:59
3

2020-01-01 00:00:00
2020-07-01 23:59:59
3

2020-01-01 00:00:00
2020-12-31 23:59:59
3

2020-01-01 00:00:00
2021-01-01 23:59:59
3

2020-07-01 00:00:00
2021-01-01 23:59:59
0

2021-01-01 00:00:00
2021-01-01 23:59:59
0


SELECT DISTINCT period_start, 
                period_end, 
                new,
                COUNT(dt2.cancelled) over (partition by period_start, period_end) as cancelled
FROM (SELECT DISTINCT period_start, 
                      period_end, 
                     count(dt1.id) over (partition by period_start, period_end) as new
     FROM periods
LEFT JOIN (SELECT DISTINCT id, min(start) over (partition by id) as start FROM dt) as dt1
       ON dt1.start between period_start and period_end) u
LEFT JOIN (SELECT id, cancelled FROM dt group by id, cancelled) dt2
       ON dt2.cancelled between period_start and period_end


period_start
period_end
new
cancelled


2020-01-01 00:00:00
2020-01-01 23:59:59
3
0

2020-01-01 00:00:00
2020-06-30 23:59:59
3
1

2020-01-01 00:00:00
2020-07-01 23:59:59
3
1

2020-01-01 00:00:00
2020-12-31 23:59:59
3
2

2020-01-01 00:00:00
2021-01-01 23:59:59
3
2

2020-07-01 00:00:00
2021-01-01 23:59:59
0
1

2021-01-01 00:00:00
2021-01-01 23:59:59
0
0


WITH 
  tab_start as (SELECT DISTINCT id, min(start) over (partition by id) as start FROM dt),
  tab_cancelled as (SELECT DISTINCT id, cancelled FROM dt WHERE cancelled IS NOT NULL),
  tab_rejoined as (SELECT DISTINCT dt.id, dt.start as rejoined FROM dt INNER JOIN tab_cancelled tc ON tc.id = dt.id AND dt.start > tc.cancelled)
SELECT DISTINCT period_start,
                period_end,
                new,
                cancelled,
                COUNT(dt3.id) over (partition by period_start, period_end) as rejoined
FROM (SELECT DISTINCT period_start, 
                 period_end, 
                 new,
                 COUNT(dt2.id) over (partition by period_start, period_end) as cancelled
FROM (SELECT DISTINCT period_start, 
                      period_end, 
                     COUNT(dt1.id) over (partition by period_start, period_end) as new
     FROM periods
LEFT JOIN tab_start as dt1
       ON dt1.start between period_start and period_end) u
LEFT JOIN tab_cancelled as dt2
       ON dt2.cancelled between period_start and period_end) v
LEFT JOIN tab_rejoined as dt3
       ON dt3.rejoined between period_start and period_end


period_start
period_end
new
cancelled
rejoined


2020-01-01 00:00:00
2020-01-01 23:59:59
3
0
0

2020-01-01 00:00:00
2020-06-30 23:59:59
3
1
0

2020-01-01 00:00:00
2020-07-01 23:59:59
3
1
1

2020-01-01 00:00:00
2020-12-31 23:59:59
3
2
1

2020-01-01 00:00:00
2021-01-01 23:59:59
3
2
2

2020-07-01 00:00:00
2021-01-01 23:59:59
0
1
2

2021-01-01 00:00:00
2021-01-01 23:59:59
0
0
1


WITH 
  tab_start as (SELECT DISTINCT id, min(start) over (partition by id) as start FROM dt),
  tab_cancelled as (SELECT DISTINCT id, cancelled FROM dt WHERE cancelled IS NOT NULL),
  tab_rejoined as (SELECT DISTINCT dt.id, dt.start as rejoined FROM dt INNER JOIN tab_cancelled tc ON tc.id = dt.id AND dt.start > tc.cancelled)
SELECT DISTINCT period_start, 
                 period_end, 
                 COUNT(DISTINCT dt1.id) as new,
                 COUNT(DISTINCT dt2.id, dt2.cancelled) as cancelled,
                 COUNT(DISTINCT dt3.id, dt3.rejoined) as rejoined
     FROM periods
LEFT JOIN tab_start as dt1
       ON dt1.start between period_start and period_end
LEFT JOIN tab_cancelled as dt2
       ON dt2.cancelled between period_start and period_end
LEFT JOIN tab_rejoined as dt3
       ON dt3.rejoined between period_start and period_end
GROUP BY period_start, period_end


period_start
period_end
new
cancelled
rejoined


2020-01-01 00:00:00
2020-01-01 23:59:59
3
0
0

2020-01-01 00:00:00
2020-06-30 23:59:59
3
1
0

2020-01-01 00:00:00
2020-07-01 23:59:59
3
1
1

2020-01-01 00:00:00
2020-12-31 23:59:59
3
2
1

2020-01-01 00:00:00
2021-01-01 23:59:59
3
2
2

2020-07-01 00:00:00
2021-01-01 23:59:59
0
1
2

2021-01-01 00:00:00
2021-01-01 23:59:59
0
0
1


WITH 
  tab_start as (SELECT DISTINCT id, min(start) over (partition by id) as start FROM dt),
  tab_cancelled as (SELECT DISTINCT id, cancelled FROM dt WHERE cancelled IS NOT NULL),
  tab_rejoined as (SELECT DISTINCT dt.id, dt.start as rejoined FROM dt INNER JOIN tab_cancelled tc ON tc.id = dt.id AND dt.start > tc.cancelled)
SELECT DISTINCT period_start, 
                period_end, 
                COUNT(DISTINCT dt1.id) as new,
                COUNT(DISTINCT dt2.id) as cancelled,
                COUNT(DISTINCT dt3.id) as rejoined
     FROM periods
LEFT JOIN tab_start as dt1
       ON dt1.start between period_start and period_end
LEFT JOIN tab_cancelled as dt2
       ON dt2.cancelled between period_start and period_end
LEFT JOIN tab_rejoined as dt3
       ON dt3.rejoined between period_start and period_end
GROUP BY period_start, period_end


period_start
period_end
new
cancelled
rejoined


2020-01-01 00:00:00
2020-01-01 23:59:59
3
0
0

2020-01-01 00:00:00
2020-06-30 23:59:59
3
1
0

2020-01-01 00:00:00
2020-07-01 23:59:59
3
1
1

2020-01-01 00:00:00
2020-12-31 23:59:59
3
2
1

2020-01-01 00:00:00
2021-01-01 23:59:59
3
2
2

2020-07-01 00:00:00
2021-01-01 23:59:59
0
1
2

2021-01-01 00:00:00
2021-01-01 23:59:59
0
0
1
msid	id	start	cancelled
1	1	2020-01-01 09:00:00	null
2	2	2020-01-01 09:00:00	2020-12-31 09:00:00
3	2	2021-01-01 09:00:00	null
4	3	2020-01-01 09:00:00	2020-06-30 09:00:00
5	3	2020-02-01 09:00:00	2020-06-30 09:00:00
6	3	2020-07-01 09:00:00	null
period_start	period_end
2020-01-01 00:00:00	2020-01-01 23:59:59
2020-01-01 00:00:00	2020-06-30 23:59:59
2020-01-01 00:00:00	2020-07-01 23:59:59
2020-01-01 00:00:00	2020-12-31 23:59:59
2020-01-01 00:00:00	2021-01-01 23:59:59
2020-07-01 00:00:00	2021-01-01 23:59:59
2021-01-01 00:00:00	2021-01-01 23:59:59