Postgres 16

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

create table companies
(
    id               bigserial primary key,
    created_at       timestamp(6),  
    name             varchar(200),   
    status           varchar(200)
);
/*
select setseed(.42);
insert into companies (created_at,name,status)
select now()-random()*'4 months'::interval,
       left(gen_random_uuid()::text,floor(random()*15)::int),
       case when random()>.5 then 'Active' else 'Blocked' end
from generate_series(1,15,1)
returning *;
  */
insert into companies (created_at,name,status) values
  ('2023-10-07 18:06:55.9','Right before custom October start','Active'),
  ('2023-10-07 18:06:56.01','Right after custom October start','Blocked'),
  (now()-'1year'::interval-random()*'4 months'::interval,
   left(gen_random_uuid()::text,floor(random()*15)::int),
   case when random()>.5 then 'Active' else 'Blocked' end),
  (now()-'1year'::interval-random()*'4 months'::interval,
   left(gen_random_uuid()::text,floor(random()*15)::int),
   case when random()>.5 then 'Active' else 'Blocked' end)
returning *;

CREATE TABLE




id
created_at
name
status


1
2023-10-07 18:06:55.9
Right before custom October start
Active

2
2023-10-07 18:06:56.01
Right after custom October start
Blocked

3
2024-01-26 20:53:40.884863
d67fdb
Blocked

4
2024-01-18 09:45:30.939263
8
Active



INSERT 0 4


select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                    +'6d 18:06:56'::interval as created_at,
       count(*)filter(where status='Active') as "Active",
       count(*)filter(where status='Blocked') as "Blocked"
from companies
group by 1 order by 1;


created_at
Active
Blocked


2023-09-07 18:06:56
1
0

2023-10-07 18:06:56
0
1

2024-01-07 18:06:56
1
1



SELECT 3


select date_trunc('month',created_at-interval '6d 18h06m56s')
                                    +interval '6d 18h06m56s' as created_at,
       count(*)filter(where status='Active') as "Active",
       count(*)filter(where status='Blocked') as "Blocked"
from companies
group by 1 order by 1;


created_at
Active
Blocked


2023-09-07 18:06:56
1
0

2023-10-07 18:06:56
0
1

2024-01-07 18:06:56
1
1



SELECT 3

id	created_at	name	status
1	2023-10-07 18:06:55.9	Right before custom October start	Active
2	2023-10-07 18:06:56.01	Right after custom October start	Blocked
3	2024-01-26 20:53:40.884863	d67fdb	Blocked
4	2024-01-18 09:45:30.939263	8	Active

created_at	Active	Blocked
2023-09-07 18:06:56	1	0
2023-10-07 18:06:56	0	1
2024-01-07 18:06:56	1	1