Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (
 id int, 
 transaction_date date
)
insert into test (id, transaction_date) values
  (1       , '2021-10-01 00:00:00') 
, (2       , '2021-10-01 00:00:00') 
, (3       , '2021-10-02 00:00:00') 
, (4       , '2021-10-03 00:00:00') 
, (5       , '2021-10-04 00:00:00') 
, (6       , '2021-10-05 00:00:00') 
, (7       , '2021-10-06 00:00:00') 

, (1       , '2021-11-01 00:00:00') 
, (2       , '2021-11-01 00:00:00') 
, (3       , '2021-11-02 00:00:00') 

, (1       , '2021-12-01 00:00:00') 
, (2       , '2021-12-01 00:00:00') 
, (3       , '2021-12-02 00:00:00')
, (4       , '2021-12-03 00:00:00') 
, (8       , '2021-12-04 00:00:00') 
, (8       , '2021-12-05 00:00:00') 
, (9       , '2021-12-06 00:00:00') 
, (9       , '2021-12-07 00:00:00') 
18 rows affected

select 
  date(date_trunc('month', transaction_date)) as "Date"
, count(distinct id) as "Count"
from test t
where transaction_date >= date_trunc('month', current_date)
  and transaction_date < (date_trunc('month', current_date)+interval '1 month') 
  and not exists ( -- not previous month
      select 1
      from test t2
      where t2.id = t.id
        and t2.transaction_date >= date_trunc('month', current_date) - interval '1 month'
        and t2.transaction_date <  date_trunc('month', current_date)
  
  )
  and exists ( -- created previously
      select 1
      from test t2
      where t2.id = t.id
        and t2.transaction_date <  date_trunc('month', current_date)
  )
group by date_trunc('month', transaction_date)
  
  


Date
Count


2021-12-01
1


create temporary table tmp_test
(
 transaction_date date not null, 
 id int not null
)
insert into tmp_test
(transaction_date, id)
select distinct
 date_trunc('month', transaction_date) as trans_month, id
from test
where transaction_date >= date_trunc('month', current_date)
  and transaction_date < (date_trunc('month', current_date)+interval '1 month') 
6 rows affected

create index idx_tmp_test on test(transaction_date, id)
select * from tmp_test


transaction_date
id


2021-12-01
1

2021-12-01
2

2021-12-01
3

2021-12-01
4

2021-12-01
8

2021-12-01
9


delete from tmp_test tmp
where exists ( -- not keeping those also in previous month
      select 1
      from test t
      where t.id = tmp.id
        and t.transaction_date >= date_trunc('month', current_date) - interval '1 month'
        and t.transaction_date <  date_trunc('month', current_date)
  )
3 rows affected

select * from tmp_test


transaction_date
id


2021-12-01
4

2021-12-01
8

2021-12-01
9


delete from tmp_test tmp
where not exists ( -- not keeping the fresh ones
      select 1
      from test t2
      where t2.id = tmp.id
        and t2.transaction_date <  tmp.transaction_date
  );
2 rows affected

select 
  to_char(transaction_date, 'YYYY-MM') as transaction_month
, count(id) as total_id
from tmp_test
group by transaction_date


transaction_month
total_id


2021-12
1
transaction_date	id
2021-12-01	1
2021-12-01	2
2021-12-01	3
2021-12-01	4
2021-12-01	8
2021-12-01	9