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.
createor replacefunction site_aggregates_activity(i text)
returnsintlanguage plpgsql
as
$$declare
count_ integer;
beginselect count(*)
into count_
from (
select c.creator_id fromcomment c
innerjoin user_ u on c.creator_id = u.id
where c.published > ('now'::timestamp - i::interval)
and u.local = trueunionselect p.creator_id from post p
innerjoin 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'));
What does “active” mean? Are lurkers considered active? Or do you have to vote/comment/post to be considered active?
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'));