By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
id VARCHAR(20),
s_id INT,
date DATE);
INSERT INTO mytable VALUES
('X',8,'2022-03-23'),
('X',9,'2022-03-24'),
('X',9,'2022-03-24'),
('X',10,'2022-03-24');
SELECT t1.date, t1.s_id, COUNT(t2.s_id)
FROM
(SELECT DISTINCT A.date, B.s_id FROM mytable A CROSS JOIN mytable B) t1
LEFT JOIN mytable t2
ON t1.date=t2.date
AND t1.s_id=t2.s_id
GROUP BY t1.date, t1.s_id
ORDER BY t1.date, t1.s_id
| date | s_id | COUNT(t2.s_id) |
|---|---|---|
| 2022-03-23 | 8 | 1 |
| 2022-03-23 | 9 | 0 |
| 2022-03-23 | 10 | 0 |
| 2022-03-24 | 8 | 0 |
| 2022-03-24 | 9 | 2 |
| 2022-03-24 | 10 | 1 |