Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create temp table my_temp_table(client_id int,id int,is_batch_empty boolean);
insert into my_temp_table values
 (1,1,true)
,(1,2,false)
,(1,3,true)
,(1,4,true)
,(2,5,false)
,(2,6,true)
,(2,7,true)
returning *;

CREATE TABLE




client_id
id
is_batch_empty


1
1
t

1
2
f

1
3
t

1
4
t

2
5
f

2
6
t

2
7
t



INSERT 0 7


select distinct a.id
      , sum(case when a.is_batch_empty then 1 else 0 end)over w1 as empty_count
from my_temp_table a
window w1 as (partition by a.client_id order by a.id
              range between unbounded preceding and current row)
order by 1;


id
empty_count


1
1

2
1

3
2

4
3

5
0

6
1

7
2



SELECT 7


select distinct a.id
      , sum(case when a.is_batch_empty then 1 else 0 end)over w1 as empty_count
from my_temp_table a
window w1 as (partition by a.client_id order by a.id
              rows between unbounded preceding and current row)
order by 1;


id
empty_count


1
1

2
1

3
2

4
3

5
0

6
1

7
2



SELECT 7


truncate my_temp_table;
--making id non-unique
insert into my_temp_table values
 (1,1,true)
,(1,2,false)
,(1,3,true)
,(1,3,true)
,(2,5,false)
,(2,5,true)
,(2,7,true)
returning *;

TRUNCATE TABLE




client_id
id
is_batch_empty


1
1
t

1
2
f

1
3
t

1
3
t

2
5
f

2
5
t

2
7
t



INSERT 0 7


select distinct a.id
      , sum(case when a.is_batch_empty then 1 else 0 end)over w1 as empty_count
from my_temp_table a
window w1 as (partition by a.client_id order by a.id
              range between unbounded preceding and current row)
order by 1;


id
empty_count


1
1

2
1

3
3

5
1

7
2



SELECT 5


select distinct a.id
      , sum(case when a.is_batch_empty then 1 else 0 end)over w1 as empty_count
from my_temp_table a
window w1 as (partition by a.client_id order by a.id
              rows between unbounded preceding and current row)
order by 1;


id
empty_count


1
1

2
1

3
2

3
3

5
1

5
0

7
2



SELECT 7


--this emulates a `range` frame_clause
select distinct a.id,
       (select sum(case when t2.is_batch_empty then 1 else 0 end)
        from my_temp_table t2
        where a.client_id=t2.client_id
          and a.id>=t2.id)
from my_temp_table a
order by 1;


id
sum


1
1

2
1

3
3

5
1

7
2



SELECT 5