By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table customers (customer text, inicial_date date, limit_date date);
insert into customers values ("Phill", "2023-01-01", "2023-06-01"), ("Jhon", "2023-03-01", "2023-07-01"), ("Ellie", "2023-09-01", "2023-10-01"), ("Mark", "2023-12-01", "2024-03-01");
Records: 4 Duplicates: 0 Warnings: 0
with recursive cte(dt, cnt) as (
select t.dt, (select sum(c1.inicial_date <= t.dt and t.dt <= c1.limit_date)
from customers c1)
from (select min(c.inicial_date) dt from customers c) t
union all
select c.dt + interval 1 month, (select sum(c1.inicial_date <= c.dt + interval 1 month
and c.dt + interval 1 month <= c1.limit_date)
from customers c1)
from cte c where c.dt < (select max(c1.limit_date) from customers c1)
)
select year(dt), month(dt), cnt from cte;
| year(dt) | month(dt) | cnt |
|---|---|---|
| 2023 | 1 | 1 |
| 2023 | 2 | 1 |
| 2023 | 3 | 2 |
| 2023 | 4 | 2 |
| 2023 | 5 | 2 |
| 2023 | 6 | 2 |
| 2023 | 7 | 1 |
| 2023 | 8 | 0 |
| 2023 | 9 | 1 |
| 2023 | 10 | 1 |
| 2023 | 11 | 0 |
| 2023 | 12 | 1 |
| 2024 | 1 | 1 |
| 2024 | 2 | 1 |
| 2024 | 3 | 1 |