migrations/2021-01-27-202728_active_users_monthly/up.sql

   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;
   5
   6 ALTER TABLE site_aggregates
   7     ADD COLUMN users_active_week bigint NOT NULL DEFAULT 0;
   8
   9 ALTER TABLE site_aggregates
  10     ADD COLUMN users_active_month bigint NOT NULL DEFAULT 0;
  11
  12 ALTER TABLE site_aggregates
  13     ADD COLUMN users_active_half_year bigint NOT NULL DEFAULT 0;
  14
  15 ALTER TABLE community_aggregates
  16     ADD COLUMN users_active_day bigint NOT NULL DEFAULT 0;
  17
  18 ALTER TABLE community_aggregates
  19     ADD COLUMN users_active_week bigint NOT NULL DEFAULT 0;
  20
  21 ALTER TABLE community_aggregates
  22     ADD COLUMN users_active_month bigint NOT NULL DEFAULT 0;
  23
  24 ALTER TABLE community_aggregates
  25     ADD COLUMN users_active_half_year bigint NOT NULL DEFAULT 0;
  26
  27 CREATE OR REPLACE FUNCTION site_aggregates_activity (i text)
  28     RETURNS int
  29     LANGUAGE plpgsql
  30     AS $$
  31 DECLARE
  32     count_ integer;
  33 BEGIN
  34     SELECT
  35         count(*) INTO count_
  36     FROM (
  37         SELECT
  38             c.creator_id
  39         FROM
  40             comment c
  41             INNER JOIN user_ u ON c.creator_id = u.id
  42         WHERE
  43             c.published > ('now'::timestamp - i::interval)
  44             AND u.local = TRUE
  45         UNION
  46         SELECT
  47             p.creator_id
  48         FROM
  49             post p
  50             INNER JOIN user_ u ON p.creator_id = u.id
  51         WHERE
  52             p.published > ('now'::timestamp - i::interval)
  53             AND u.local = TRUE) a;
  54     RETURN count_;
  55 END;
  56 $$;
  57
  58 UPDATE
  59     site_aggregates
  60 SET
  61     users_active_day = (
  62         SELECT
  63             *
  64         FROM
  65             site_aggregates_activity ('1 day'));
  66
  67 UPDATE
  68     site_aggregates
  69 SET
  70     users_active_week = (
  71         SELECT
  72             *
  73         FROM
  74             site_aggregates_activity ('1 week'));
  75
  76 UPDATE
  77     site_aggregates
  78 SET
  79     users_active_month = (
  80         SELECT
  81             *
  82         FROM
  83             site_aggregates_activity ('1 month'));
  84
  85 UPDATE
  86     site_aggregates
  87 SET
  88     users_active_half_year = (
  89         SELECT
  90             *
  91         FROM
  92             site_aggregates_activity ('6 months'));
  93
  94 CREATE OR REPLACE FUNCTION community_aggregates_activity (i text)
  95     RETURNS TABLE (
  96         count_ bigint,
  97         community_id_ integer)
  98     LANGUAGE plpgsql
  99     AS $$
 100 BEGIN
 101     RETURN query
 102     SELECT
 103         count(*),
 104         community_id
 105     FROM (
 106         SELECT
 107             c.creator_id,
 108             p.community_id
 109         FROM
 110             comment c
 111             INNER JOIN post p ON c.post_id = p.id
 112         WHERE
 113             c.published > ('now'::timestamp - i::interval)
 114         UNION
 115         SELECT
 116             p.creator_id,
 117             p.community_id
 118         FROM
 119             post p
 120         WHERE
 121             p.published > ('now'::timestamp - i::interval)) a
 122 GROUP BY
 123     community_id;
 124 END;
 125 $$;
 126
 127 UPDATE
 128     community_aggregates ca
 129 SET
 130     users_active_day = mv.count_
 131 FROM
 132     community_aggregates_activity ('1 day') mv
 133 WHERE
 134     ca.community_id = mv.community_id_;
 135
 136 UPDATE
 137     community_aggregates ca
 138 SET
 139     users_active_week = mv.count_
 140 FROM
 141     community_aggregates_activity ('1 week') mv
 142 WHERE
 143     ca.community_id = mv.community_id_;
 144
 145 UPDATE
 146     community_aggregates ca
 147 SET
 148     users_active_month = mv.count_
 149 FROM
 150     community_aggregates_activity ('1 month') mv
 151 WHERE
 152     ca.community_id = mv.community_id_;
 153
 154 UPDATE
 155     community_aggregates ca
 156 SET
 157     users_active_half_year = mv.count_
 158 FROM
 159     community_aggregates_activity ('6 months') mv
 160 WHERE
 161     ca.community_id = mv.community_id_;
 162