Postgres 14

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

CREATE TABLE my_table (USER_ID text, START_DATE date, END_DATE date) ;

INSERT INTO my_table VALUES

('A', '03/07/2020', '31/07/2020'),

('A', '05/06/2020', '03/07/2020'),

('A', '08/05/2020', '05/06/2020'),

('A', '10/04/2020', '08/05/2020'),

('B', '13/02/2020', '12/03/2020'),

('B', '16/01/2020', '13/02/2020'),

('C', '22/05/2020', '19/06/2020'),

('C', '24/04/2020', '22/05/2020'),

('D', '25/09/2020', '23/10/2020'),

('D', '28/08/2020', '25/09/2020'),

('D', '31/07/2020', '28/08/2020'),

('D', '03/07/2020', '31/07/2020'),

('D', '05/06/2020', '03/07/2020'),

('E', '25/11/2020', '23/12/2020'),

('E', '28/10/2020', '25/11/2020'),

('E', '30/09/2020', '28/10/2020'),

('F', '14/2/2020', '13/3/2020'),

('F', '17/1/2020', '14/2/2020'),

('F', '20/12/2019', '17/1/2020'),

('F', '22/11/2019', '20/12/2019'),

('G', '7/11/2020', '5/12/2020'),

('G', '10/10/2020', '7/11/2020');

CREATE TABLE

INSERT 0 22

WITH m AS

( SELECT generate_series(min(date_trunc('month', start_date)), max(end_date), '1 month') :: date AS month

FROM my_table AS t

)

SELECT to_char(m.month, 'YYYY') AS year

, to_char(m.month, 'MM') AS month

, count(DISTINCT t.user_id) AS "count(distinct user_id)"

FROM my_table AS t

RIGHT JOIN m

ON daterange(t.start_date, t.end_date) @> m.month

GROUP BY m.month

ORDER BY m.month

SELECT 14