General Discussion

Looking through the code on GitHub it looks like active is the number of new posts plus the number of new comments in a certain time period (in this case 6 months).

Caveat: I'm just a hobbyist so my reading of the code may not be exactly correct.

create or replace function site_aggregates_activity(i text)
returns int
language plpgsql
as
$$
declare
   count_ integer;
begin
  select count(*) 
  into count_
  from (
    select c.creator_id from comment c
    inner join user_ u on c.creator_id = u.id
    where c.published > ('now'::timestamp - i::interval) 
    and u.local = true
    union
    select p.creator_id from post p
    inner join user_ u on p.creator_id = u.id
    where p.published > ('now'::timestamp - i::interval)
    and u.local = true
  ) a;
  return count_;
end;
$$;

update site_aggregates 
set users_active_day = (select * from site_aggregates_activity('1 day'));

update site_aggregates 
set users_active_week = (select * from site_aggregates_activity('1 week'));

update site_aggregates 
set users_active_month = (select * from site_aggregates_activity('1 month'));

update site_aggregates 
set users_active_half_year = (select * from site_aggregates_activity('6 months'));