Postgres 10

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

select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


create table tbl as
select'1/1/2020' as date, 'A' as group, 1 as cummulativecount
UNION
select'1/2/2020' as date, 'A' as group, 3 as cummulativecount
UNION
select'1/2/2020' as date, 'B' as group, 1 as cummulativecount
UNION
select'1/3/2020' as date, 'B' as group, 2 as cummulativecount
4 rows affected

create table ads as 
SELECT
    sq."date",
    sq."group",
    COALESCE(m."cummulativecount",0) as CummulativeCount
FROM 
(
    SELECT "date", "group" 
    FROM 
    (
        SELECT DISTINCT "date"
        FROM tbl
    ) dt
    
    CROSS JOIN 
    (
        SELECT DISTINCT "group"
        FROM tbl
    ) grp
) sq 
LEFT JOIN tbl m ON m."date"=sq."date" AND
                       m."group" = sq."group"
6 rows affected

create table prev_cnt as 
select t."group", t.cummulativecount 
from tbl as t
inner join (
select tbl."group", max(tbl."date") m_date
from ads 
join tbl
on ads."group"=tbl."group"
where ads.cummulativecount =0 and
tbl."date"<ads."date"
group by tbl."group"
) as sq
on t."group"=sq."group" and t."date"=sq.m_date
1 rows affected


create table fin_ads as 
select ads."date", ads."group", 
case 
  when ads.cummulativecount=0 and pc.cummulativecount IS NOT NULL then pc.cummulativecount 
  else ads.cummulativecount 
end as cummulativecount
 from ads
LEFT join prev_cnt as pc
on ads."group"=pc."group"
6 rows affected

select * from fin_ads
order by "date", "group"


date
group
cummulativecount


1/1/2020
A
1

1/1/2020
B
0

1/2/2020
A
3

1/2/2020
B
1

1/3/2020
A
3

1/3/2020
B
2

date	group	cummulativecount
1/1/2020	A	1
1/1/2020	B	0
1/2/2020	A	3
1/2/2020	B	1
1/3/2020	A	3
1/3/2020	B	2