Oracle 18c

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

create table person (name, date_created) as
select 'person_1', date '2020-05-04' + (level/1440) from dual connect by level <= 220
union all
select 'person_1', date '2020-05-06' + (level/1440) from dual connect by level <= 30
union all
select 'person_1', date '2020-05-15' + (level/1440) from dual connect by level <= 12
union all
select 'person_1', date '2020-05-31' + (level/1440) from dual connect by level <= 30
union all
select 'person_2', date '2020-05-02' + (level/1440) from dual connect by level <= 7
union all
select 'person_2', date '2020-05-21' + (level/1440) from dual connect by level <= 9
308 rows affected

with days (day) as (
  select date '2020-05-01' from dual
  union all
  select day + 1 from days where day < last_day(day)
),
names (name) as (
  select distinct name from person
  -- possibly only within the target month?
)
select d.day, n.name,
  coalesce(
    last_value(nullif(count(p.name),0) ignore nulls)
      over (partition by n.name order by d.day), 0) as count_person
from days d
cross join names n
left join person p on p.name = n.name
and p.date_created >= d.day
and p.date_created < d.day + 1
group by d.day, n.name
order by n.name, d.day


DAY
NAME
COUNT_PERSON


01-MAY-20
person_1
0

02-MAY-20
person_1
0

03-MAY-20
person_1
0

04-MAY-20
person_1
220

05-MAY-20
person_1
220

06-MAY-20
person_1
30

07-MAY-20
person_1
30

08-MAY-20
person_1
30

09-MAY-20
person_1
30

10-MAY-20
person_1
30

11-MAY-20
person_1
30

12-MAY-20
person_1
30

13-MAY-20
person_1
30

14-MAY-20
person_1
30

15-MAY-20
person_1
12

16-MAY-20
person_1
12

17-MAY-20
person_1
12

18-MAY-20
person_1
12

19-MAY-20
person_1
12

20-MAY-20
person_1
12

21-MAY-20
person_1
12

22-MAY-20
person_1
12

23-MAY-20
person_1
12

24-MAY-20
person_1
12

25-MAY-20
person_1
12

26-MAY-20
person_1
12

27-MAY-20
person_1
12

28-MAY-20
person_1
12

29-MAY-20
person_1
12

30-MAY-20
person_1
12

31-MAY-20
person_1
30

01-MAY-20
person_2
0

02-MAY-20
person_2
7

03-MAY-20
person_2
7

04-MAY-20
person_2
7

05-MAY-20
person_2
7

06-MAY-20
person_2
7

07-MAY-20
person_2
7

08-MAY-20
person_2
7

09-MAY-20
person_2
7

10-MAY-20
person_2
7

11-MAY-20
person_2
7

12-MAY-20
person_2
7

13-MAY-20
person_2
7

14-MAY-20
person_2
7

15-MAY-20
person_2
7

16-MAY-20
person_2
7

17-MAY-20
person_2
7

18-MAY-20
person_2
7

19-MAY-20
person_2
7

20-MAY-20
person_2
7

21-MAY-20
person_2
9

22-MAY-20
person_2
9

23-MAY-20
person_2
9

24-MAY-20
person_2
9

25-MAY-20
person_2
9

26-MAY-20
person_2
9

27-MAY-20
person_2
9

28-MAY-20
person_2
9

29-MAY-20
person_2
9

30-MAY-20
person_2
9

31-MAY-20
person_2
9


select  Y.name, ROUND(SUM(Y.final_count)/  ( 31 ) ) AVG_COUNT FROM
(SELECT day, name,--final_count,
nvl(last_value(decode(final_count, 0, cast(null as number), final_count)) ignore nulls over (order by day, final_count desc), 0) final_count

FROM(

with days (day) as (
  select date '2020-05-01' from dual
  union all
  select day + 1 from days where day < last_day(day)
),
names (name) as (
  select distinct name from person
)
select d.day, n.name, count(p.name) as final_count
from days d
cross join names n
left join person p on p.name = n.name
and p.date_created >= d.day
and p.date_created < d.day + 1
group by d.day, n.name
order by d.day, n.name) x
where x.name= ( 'person_1')
)Y   
GROUP BY Y.name;


NAME
AVG_COUNT


person_1
30


select  Y.name, ROUND(SUM(Y.final_count)/  ( 31 ) ) AVG_COUNT FROM
(SELECT day, name,--final_count,
nvl(last_value(decode(final_count, 0, cast(null as number), final_count)) ignore nulls over (order by day, final_count desc), 0) final_count

FROM(

with days (day) as (
  select date '2020-05-01' from dual
  union all
  select day + 1 from days where day < last_day(day)
),
names (name) as (
  select distinct name from person
)
select d.day, n.name, count(p.name) as final_count
from days d
cross join names n
left join person p on p.name = n.name
and p.date_created >= d.day
and p.date_created < d.day + 1
group by d.day, n.name
order by d.day, n.name) x
--where x.name= ( 'person_1')
)Y   
GROUP BY Y.name;


NAME
AVG_COUNT


person_2
30

person_1
30


select p.name, round(count(*) / extract(day from last_day(date '2020-05-01'))) as avg_count
from person p
where p.date_created >= date '2020-05-01'
and p.date_created < date '2020-05-01' + interval '1' month
group by p.name
order by p.name;


NAME
AVG_COUNT


person_1
9

person_2
1

DAY	NAME	COUNT_PERSON
01-MAY-20	person_1	0
02-MAY-20	person_1	0
03-MAY-20	person_1	0
04-MAY-20	person_1	220
05-MAY-20	person_1	220
06-MAY-20	person_1	30
07-MAY-20	person_1	30
08-MAY-20	person_1	30
09-MAY-20	person_1	30
10-MAY-20	person_1	30
11-MAY-20	person_1	30
12-MAY-20	person_1	30
13-MAY-20	person_1	30
14-MAY-20	person_1	30
15-MAY-20	person_1	12
16-MAY-20	person_1	12
17-MAY-20	person_1	12
18-MAY-20	person_1	12
19-MAY-20	person_1	12
20-MAY-20	person_1	12
21-MAY-20	person_1	12
22-MAY-20	person_1	12
23-MAY-20	person_1	12
24-MAY-20	person_1	12
25-MAY-20	person_1	12
26-MAY-20	person_1	12
27-MAY-20	person_1	12
28-MAY-20	person_1	12
29-MAY-20	person_1	12
30-MAY-20	person_1	12
31-MAY-20	person_1	30
01-MAY-20	person_2	0
02-MAY-20	person_2	7
03-MAY-20	person_2	7
04-MAY-20	person_2	7
05-MAY-20	person_2	7
06-MAY-20	person_2	7
07-MAY-20	person_2	7
08-MAY-20	person_2	7
09-MAY-20	person_2	7
10-MAY-20	person_2	7
11-MAY-20	person_2	7
12-MAY-20	person_2	7
13-MAY-20	person_2	7
14-MAY-20	person_2	7
15-MAY-20	person_2	7
16-MAY-20	person_2	7
17-MAY-20	person_2	7
18-MAY-20	person_2	7
19-MAY-20	person_2	7
20-MAY-20	person_2	7
21-MAY-20	person_2	9
22-MAY-20	person_2	9
23-MAY-20	person_2	9
24-MAY-20	person_2	9
25-MAY-20	person_2	9
26-MAY-20	person_2	9
27-MAY-20	person_2	9
28-MAY-20	person_2	9
29-MAY-20	person_2	9
30-MAY-20	person_2	9
31-MAY-20	person_2	9