actions, PostgreSQL

Language: Layout:

drop table if exists action cascade;
create table action (id uuid, type text, status text, created timestamp, live_at timestamp, expired_at timestamp);

insert into action
values
('10f1dc79-61b7-46a4-ad66-55e2a68b7148','FACEBOOK_SOCIAL_SHARE','EXPIRED', timestamp '2019-06-21 11:28:31', timestamp '2019-07-21 11:28:36', timestamp '2019-10-02 11:40:27'),
('3e59ccd4-a795-4e74-b841-4da1e57fb51f','FACEBOOK_SOCIAL_SHARE','LIVE', timestamp '2019-10-04 18:25:57', timestamp '2019-10-04 18:25:57', NULL);

with actions as (
  select id, type, 
         array(select extract(month from x.dt)::int
               from generate_series(date_trunc('month', live_at), 
                                    date_trunc('month', coalesce(expired_at, current_timestamp)) + interval '1 month' - interval '1 day', 
                                    interval '1 month') as x(dt)) as months_live
  from  action
)
select m.month, type, count(distinct a.id)
from generate_series(1,12) as m(month)
  left join actions a on m.month = any(a.months_live)
group by m.month, type;


absolute service time: 0,54 sec