1 -- Add monthly and half yearly active columns for site and community aggregates
3 -- These columns don't need to be updated with a trigger, so they're saved daily via queries
4 alter table site_aggregates add column users_active_day bigint not null default 0;
5 alter table site_aggregates add column users_active_week bigint not null default 0;
6 alter table site_aggregates add column users_active_month bigint not null default 0;
7 alter table site_aggregates add column users_active_half_year bigint not null default 0;
9 alter table community_aggregates add column users_active_day bigint not null default 0;
10 alter table community_aggregates add column users_active_week bigint not null default 0;
11 alter table community_aggregates add column users_active_month bigint not null default 0;
12 alter table community_aggregates add column users_active_half_year bigint not null default 0;
14 create or replace function site_aggregates_activity(i text)
25 select c.creator_id from comment c
26 inner join user_ u on c.creator_id = u.id
27 where c.published > ('now'::timestamp - i::interval)
30 select p.creator_id from post p
31 inner join user_ u on p.creator_id = u.id
32 where p.published > ('now'::timestamp - i::interval)
39 update site_aggregates
40 set users_active_day = (select * from site_aggregates_activity('1 day'));
42 update site_aggregates
43 set users_active_week = (select * from site_aggregates_activity('1 week'));
45 update site_aggregates
46 set users_active_month = (select * from site_aggregates_activity('1 month'));
48 update site_aggregates
49 set users_active_half_year = (select * from site_aggregates_activity('6 months'));
51 create or replace function community_aggregates_activity(i text)
52 returns table(count_ bigint, community_id_ integer)
58 select count(*), community_id
60 select c.creator_id, p.community_id from comment c
61 inner join post p on c.post_id = p.id
62 where c.published > ('now'::timestamp - i::interval)
64 select p.creator_id, p.community_id from post p
65 where p.published > ('now'::timestamp - i::interval)
67 group by community_id;
71 update community_aggregates ca
72 set users_active_day = mv.count_
73 from community_aggregates_activity('1 day') mv
74 where ca.community_id = mv.community_id_;
76 update community_aggregates ca
77 set users_active_week = mv.count_
78 from community_aggregates_activity('1 week') mv
79 where ca.community_id = mv.community_id_;
81 update community_aggregates ca
82 set users_active_month = mv.count_
83 from community_aggregates_activity('1 month') mv
84 where ca.community_id = mv.community_id_;
86 update community_aggregates ca
87 set users_active_half_year = mv.count_
88 from community_aggregates_activity('6 months') mv
89 where ca.community_id = mv.community_id_;