Db2 Developer-C 11.1

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

CREATE TABLE ActiveIDs
(
    ID INT NOT NULL,
    START_DATE DATE NOT NULL,
    END_DATE DATE NOT NULL
);
INSERT INTO ActiveIDs (ID, START_DATE, END_DATE)
VALUES 
(1001, '2023-02-02', '2023-03-05'),
(1001, '2023-03-07', '2023-11-05'),
(1002, '2023-03-05', '2023-10-20'),
(1003, '2023-03-07', '2023-04-07'),
(1004, '2023-04-07', '2023-04-17'),
(1005, '2023-04-18', '2023-05-19'),
(1006, '2023-05-19', '2023-06-14'),
(1007, '2023-05-24', '2023-06-28'),
(1008, '2022-10-31', '2022-12-01'),
(1008, '2022-12-03', '2023-10-07'),
(1009, '2022-12-01', '2023-03-22'),
(1010, '2023-04-03', '2023-05-04'),
(1011, '2023-05-04', '2023-05-23'),
(1012, '2023-05-23', '2023-10-02'),
(1013, '2023-06-15', '2023-07-21'),
(1014, '2023-01-27', '2023-02-07'),
(1015, '2023-05-02', '2023-05-18'),
(1016, '2023-05-18', '2023-06-18'),
(1017, '2023-06-18', '2023-07-27'),
(1018, '2023-05-01', '2023-09-30'),
(1019, '2022-11-22', '2023-02-22'),
(1020, '2022-02-02', '2023-05-04');
WITH dates (dt) AS (
    SELECT DATE('2022-10-01') AS dt
    FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT dt + 1 DAYS
    FROM dates
    WHERE dt < '2023-09-30'
)
SELECT 
    YEAR(d.dt) AS year
  , MONTH(d.dt) AS month
  , COUNT(DISTINCT t.ID) cd_tid
  , COUNT(DISTINCT d.dt) cd_ddt
  , (COUNT(DISTINCT t.ID) * 1.0) / (COUNT(DISTINCT d.dt) * 1.0) AS Daily_Ave
FROM dates d
LEFT JOIN ActiveIDs t ON d.dt BETWEEN t.START_DATE AND t.END_DATE
GROUP BY 
    YEAR(d.dt)
  , MONTH(d.dt)
ORDER BY 
    YEAR(d.dt)
  , MONTH(d.dt)


YEAR
MONTH
CD_TID
CD_DDT
DAILY_AVE


2022
10
2
31
0.064516129032258064

2022
11
3
30
0.100000000000000000

2022
12
4
31
0.129032258064516129

2023
1
5
31
0.161290322580645161

2023
2
6
28
0.214285714285714285

2023
3
6
31
0.193548387096774193

2023
4
8
30
0.266666666666666666

2023
5
13
31
0.419354838709677419

2023
6
10
30
0.333333333333333333

2023
7
7
31
0.225806451612903225

2023
8
5
31
0.161290322580645161

2023
9
5
30
0.166666666666666666

YEAR	MONTH	CD_TID	CD_DDT	DAILY_AVE
2022	10	2	31	0.064516129032258064
2022	11	3	30	0.100000000000000000
2022	12	4	31	0.129032258064516129
2023	1	5	31	0.161290322580645161
2023	2	6	28	0.214285714285714285
2023	3	6	31	0.193548387096774193
2023	4	8	30	0.266666666666666666
2023	5	13	31	0.419354838709677419
2023	6	10	30	0.333333333333333333
2023	7	7	31	0.225806451612903225
2023	8	5	31	0.161290322580645161
2023	9	5	30	0.166666666666666666