By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t as
select 1 as userid, 1 as isActive, date('2021-01-10') as startd, null as endd union all
select 2 as userid, 1 as isActive, date('2021-01-03') as startd, '2021-01-12'
Records: 2 Duplicates: 0 Warnings: 0
with recursive cte as (
select startd - interval weekday(startd) day as week, endd, userid
from t
where isActive = 1
union all
select week + interval 7 day, endd, userid
from cte
where (endd > week + interval 7 day or endd is null) and week < curdate()
)
select week, count(*)
from cte
group by week
order by week;
| week | count(*) |
|---|---|
| 2020-12-28 | 1 |
| 2021-01-04 | 2 |
| 2021-01-11 | 2 |
| 2021-01-18 | 1 |
| 2021-01-25 | 1 |
| 2021-02-01 | 1 |
| 2021-02-08 | 1 |
| 2021-02-15 | 1 |
| 2021-02-22 | 1 |
| 2021-03-01 | 1 |
| 2021-03-08 | 1 |
| 2021-03-15 | 1 |
| 2021-03-22 | 1 |
| 2021-03-29 | 1 |
| 2021-04-05 | 1 |
| 2021-04-12 | 1 |
| 2021-04-19 | 1 |
| 2021-04-26 | 1 |
| 2021-05-03 | 1 |
| 2021-05-10 | 1 |
| 2021-05-17 | 1 |
| 2021-05-24 | 1 |
| 2021-05-31 | 1 |
| 2021-06-07 | 1 |
| 2021-06-14 | 1 |
| 2021-06-21 | 1 |
| 2021-06-28 | 1 |
| 2021-07-05 | 1 |
| 2021-07-12 | 1 |
| 2021-07-19 | 1 |
| 2021-07-26 | 1 |
| 2021-08-02 | 1 |
| 2021-08-09 | 1 |
| 2021-08-16 | 1 |
| 2021-08-23 | 1 |
| 2021-08-30 | 1 |
| 2021-09-06 | 1 |
| 2021-09-13 | 1 |
| 2021-09-20 | 1 |
| 2021-09-27 | 1 |
| 2021-10-04 | 1 |
| 2021-10-11 | 1 |
| 2021-10-18 | 1 |
| 2021-10-25 | 1 |
| 2021-11-01 | 1 |
| 2021-11-08 | 1 |
| 2021-11-15 | 1 |
| 2021-11-22 | 1 |
| 2021-11-29 | 1 |
| 2021-12-06 | 1 |
| 2021-12-13 | 1 |
| 2021-12-20 | 1 |
| 2021-12-27 | 1 |
| 2022-01-03 | 1 |
| 2022-01-10 | 1 |
| 2022-01-17 | 1 |
| 2022-01-24 | 1 |
| 2022-01-31 | 1 |
| 2022-02-07 | 1 |
| 2022-02-14 | 1 |
| 2022-02-21 | 1 |
| 2022-02-28 | 1 |
| 2022-03-07 | 1 |
| 2022-03-14 | 1 |
| 2022-03-21 | 1 |
| 2022-03-28 | 1 |
| 2022-04-04 | 1 |
| 2022-04-11 | 1 |
| 2022-04-18 | 1 |
| 2022-04-25 | 1 |
| 2022-05-02 | 1 |
| 2022-05-09 | 1 |
| 2022-05-16 | 1 |
| 2022-05-23 | 1 |
| 2022-05-30 | 1 |
| 2022-06-06 | 1 |
| 2022-06-13 | 1 |
| 2022-06-20 | 1 |
| 2022-06-27 | 1 |
| 2022-07-04 | 1 |
| 2022-07-11 | 1 |
| 2022-07-18 | 1 |
| 2022-07-25 | 1 |
| 2022-08-01 | 1 |
| 2022-08-08 | 1 |
| 2022-08-15 | 1 |
| 2022-08-22 | 1 |
| 2022-08-29 | 1 |
| 2022-09-05 | 1 |
| 2022-09-12 | 1 |
| 2022-09-19 | 1 |
| 2022-09-26 | 1 |
| 2022-10-03 | 1 |
| 2022-10-10 | 1 |