1 -- Make sure bots aren't included in aggregate counts
3 create or replace function community_aggregates_activity(i text)
4 returns table(count_ bigint, community_id_ integer)
10 select count(*), community_id
12 select c.creator_id, p.community_id from comment c
13 inner join post p on c.post_id = p.id
14 inner join person pe on c.creator_id = pe.id
15 where c.published > ('now'::timestamp - i::interval)
16 and pe.bot_account = false
18 select p.creator_id, p.community_id from post p
19 inner join person pe on p.creator_id = pe.id
20 where p.published > ('now'::timestamp - i::interval)
21 and pe.bot_account = false
23 group by community_id;
27 create or replace function site_aggregates_activity(i text) returns integer
36 select c.creator_id from comment c
37 inner join person u on c.creator_id = u.id
38 inner join person pe on c.creator_id = pe.id
39 where c.published > ('now'::timestamp - i::interval)
41 and pe.bot_account = false
43 select p.creator_id from post p
44 inner join person u on p.creator_id = u.id
45 inner join person pe on p.creator_id = pe.id
46 where p.published > ('now'::timestamp - i::interval)
48 and pe.bot_account = false