By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Item
(
idItem int
, created_at datetime
, deleted_at datetime
);
INSERT INTO Item
VALUES
( 1, '2020-03-20 04:28:41', '2021-07-27 02:36:05')
, ( 2, '2020-03-20 04:28:41', '2021-07-27 02:36:05')
, ( 3, '2021-03-02 21:39:10', NULL )
, ( 4, '2021-03-05 21:13:13', NULL )
, ( 5, '2021-06-08 13:49:11', '2021-07-27 02:36:05')
, ( 6, '2021-07-13 02:36:05', NULL )
, ( 7, '2021-09-17 21:12:13', NULL )
SELECT t.YearMonth
, SUM(t.TotalCreated) - SUM(t.TotalDeleted) AS TotalExisting
FROM (
SELECT DATE_FORMAT(created_at, '%Y-%m') AS YearMonth
, COUNT(*) AS TotalCreated
, 0 AS TotalDeleted
FROM Item
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
UNION ALL
SELECT DATE_FORMAT(deleted_at, '%Y-%m') AS YearMonth
, 0 AS TotalCreated
, COUNT(*) AS TotalDeleted
FROM Item
WHERE deleted_at IS NOT NULL
GROUP BY DATE_FORMAT(deleted_at, '%Y-%m')
) t
GROUP BY t.YearMonth
ORDER BY t.YearMonth
| YearMonth | TotalExisting |
|---|---|
| 2020-03 | 2 |
| 2021-03 | 2 |
| 2021-06 | 1 |
| 2021-07 | -2 |
| 2021-09 | 1 |
WITH cte AS (
SELECT t.YearMonth
, SUM(t.TotalCreated) - SUM(t.TotalDeleted) AS TotalExisting
FROM (
SELECT DATE_FORMAT(created_at, '%Y-%m') AS YearMonth
, COUNT(*) AS TotalCreated
, 0 AS TotalDeleted
FROM Item
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
UNION ALL
SELECT DATE_FORMAT(deleted_at, '%Y-%m') AS YearMonth
, 0 AS TotalCreated
, COUNT(*) AS TotalDeleted
FROM Item
WHERE deleted_at IS NOT NULL
GROUP BY DATE_FORMAT(deleted_at, '%Y-%m')
) t
GROUP BY t.YearMonth
ORDER BY t.YearMonth
)
SELECT YearMonth, SUM(TotalExisting) OVER (ORDER BY YearMonth) AS TotalExisting
FROM cte;
| YearMonth | TotalExisting |
|---|---|
| 2020-03 | 2 |
| 2021-03 | 4 |
| 2021-06 | 5 |
| 2021-07 | 3 |
| 2021-09 | 4 |