MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


CREATE TABLE mytable(
   ID   VARCHAR(9) NOT NULL 
  ,`DATE` DATE 
);
INSERT INTO mytable(ID,`DATE`) VALUES ('1','2022-05-21');
INSERT INTO mytable(ID,`DATE`) VALUES ('1','2022-05-22');
INSERT INTO mytable(ID,`DATE`) VALUES ('2','2022-05-22');
INSERT INTO mytable(ID,`DATE`) VALUES ('1','2022-05-23');
INSERT INTO mytable(ID,`DATE`) VALUES ('2','2022-05-23');
INSERT INTO mytable(ID,`DATE`) VALUES ('1','2022-05-24');
INSERT INTO mytable(ID,`DATE`) VALUES ('2','2022-05-24');
INSERT INTO mytable(ID,`DATE`) VALUES ('3','2022-05-24');
SELECT `DATE` , COUNT( DISTINCT id)
FROM mytable
GROUP BY `DATE` 


DATE
COUNT( DISTINCT id)


2022-05-21
1

2022-05-22
2

2022-05-23
2

2022-05-24
3


-- only new ID's
SELECT `DATE` , COUNT( DISTINCT id)
FROM mytable
WHERE ID NOT IN (SELECT m2.ID FROM mytable m2 WHERE m2.`DATE`<mytable.`DATE`)
GROUP BY `DATE` 


DATE
COUNT( DISTINCT id)


2022-05-21
1

2022-05-22
1

2022-05-24
1


SELECT 
    m1.`DATE` , 
    sum(CASE WHEN  m2.id is null THEN 1 ELSE 0 END) as C
FROM mytable m1
LEFT JOIN mytable m2 ON m2.`DATE`<m1.`DATE` AND m2.ID=m1.ID
GROUP BY m1.`DATE` 


DATE
C


2022-05-21
1

2022-05-22
1

2022-05-23
0

2022-05-24
1


-- explanation:  only count the rows where m2.id is missing
SELECT 
    m1.`DATE` , 
    m1.id,
    m2.id,
    CASE WHEN  m2.id is null THEN 1 ELSE 0 END as C
FROM mytable m1
LEFT JOIN mytable m2 ON m2.`DATE`<m1.`DATE` AND m2.ID=m1.ID
-- GROUP BY m1.`DATE` 


DATE
id
id
C


2022-05-21
1
null
1

2022-05-22
1
1
0

2022-05-22
2
null
1

2022-05-23
1
1
0

2022-05-23
1
1
0

2022-05-23
2
2
0

2022-05-24
1
1
0

2022-05-24
1
1
0

2022-05-24
1
1
0

2022-05-24
2
2
0

2022-05-24
2
2
0

2022-05-24
3
null
1

DATE	id	id	C
2022-05-21	1	null	1
2022-05-22	1	1	0
2022-05-22	2	null	1
2022-05-23	1	1	0
2022-05-23	1	1	0
2022-05-23	2	2	0
2022-05-24	1	1	0
2022-05-24	1	1	0
2022-05-24	1	1	0
2022-05-24	2	2	0
2022-05-24	2	2	0
2022-05-24	3	null	1