Postgres 12

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

create table customer (customer_id int, signup_date date, cancel_date date);

CREATE TABLE


insert into customer
values 
  (1, date '2019-02-10', date '2020-03-05'),
  (2, date '2018-12-01', date '2019-12-29'),
  (3, date '2014-04-15', date '2019-09-15'),
  (4, date '2019-02-10', date '2020-01-04'),
  (5, date '2020-02-10', NULL       ),
  (6, date '2017-07-10', date '2018-01-01'), -- fixed this!
  (7, date '2019-09-10', date '2020-05-01'),
  (8, date '2019-02-06', NULL       ),
  (9, date '2018-02-10', date '2020-07-29'),
  (10, date '2020-02-10', date '2020-04-01');

INSERT 0 10


select to_char(g.dt::date, 'yyyy-mm'),
       count(c.customer_id) as active_customers
from generate_series(date_trunc('month', current_date) - interval '11 month', date_trunc('month', current_date), interval '1 month') as g(dt)
  left join customer c on daterange(c.signup_date, cancel_date) @> g.dt::date
group by to_char(g.dt::date, 'yyyy-mm')
order by 1


to_char
active_customers


2023-01
2

2023-02
2

2023-03
2

2023-04
2

2023-05
2

2023-06
2

2023-07
2

2023-08
2

2023-09
2

2023-10
2

2023-11
2

2023-12
2



SELECT 12

to_char	active_customers
2023-01	2
2023-02	2
2023-03	2
2023-04	2
2023-05	2
2023-06	2
2023-07	2
2023-08	2
2023-09	2
2023-10	2
2023-11	2
2023-12	2