1 -- Add monthly and half yearly active columns for site and community aggregates
2 -- These columns don't need to be updated with a trigger, so they're saved daily via queries
3 ALTER TABLE site_aggregates
4 ADD COLUMN users_active_day bigint NOT NULL DEFAULT 0;
6 ALTER TABLE site_aggregates
7 ADD COLUMN users_active_week bigint NOT NULL DEFAULT 0;
9 ALTER TABLE site_aggregates
10 ADD COLUMN users_active_month bigint NOT NULL DEFAULT 0;
12 ALTER TABLE site_aggregates
13 ADD COLUMN users_active_half_year bigint NOT NULL DEFAULT 0;
15 ALTER TABLE community_aggregates
16 ADD COLUMN users_active_day bigint NOT NULL DEFAULT 0;
18 ALTER TABLE community_aggregates
19 ADD COLUMN users_active_week bigint NOT NULL DEFAULT 0;
21 ALTER TABLE community_aggregates
22 ADD COLUMN users_active_month bigint NOT NULL DEFAULT 0;
24 ALTER TABLE community_aggregates
25 ADD COLUMN users_active_half_year bigint NOT NULL DEFAULT 0;
27 CREATE OR REPLACE FUNCTION site_aggregates_activity (i text)
41 INNER JOIN user_ u ON c.creator_id = u.id
43 c.published > ('now'::timestamp - i::interval)
50 INNER JOIN user_ u ON p.creator_id = u.id
52 p.published > ('now'::timestamp - i::interval)
53 AND u.local = TRUE) a;
65 site_aggregates_activity ('1 day'));
74 site_aggregates_activity ('1 week'));
79 users_active_month = (
83 site_aggregates_activity ('1 month'));
88 users_active_half_year = (
92 site_aggregates_activity ('6 months'));
94 CREATE OR REPLACE FUNCTION community_aggregates_activity (i text)
97 community_id_ integer)
111 INNER JOIN post p ON c.post_id = p.id
113 c.published > ('now'::timestamp - i::interval)
121 p.published > ('now'::timestamp - i::interval)) a
128 community_aggregates ca
130 users_active_day = mv.count_
132 community_aggregates_activity ('1 day') mv
134 ca.community_id = mv.community_id_;
137 community_aggregates ca
139 users_active_week = mv.count_
141 community_aggregates_activity ('1 week') mv
143 ca.community_id = mv.community_id_;
146 community_aggregates ca
148 users_active_month = mv.count_
150 community_aggregates_activity ('1 month') mv
152 ca.community_id = mv.community_id_;
155 community_aggregates ca
157 users_active_half_year = mv.count_
159 community_aggregates_activity ('6 months') mv
161 ca.community_id = mv.community_id_;