migrations/2021-08-16-004209_fix_remove_bots_from_aggregates/up.sql

   1 -- Make sure bots aren't included in aggregate counts
   2 CREATE OR REPLACE FUNCTION community_aggregates_activity (i text)
   3     RETURNS TABLE (
   4         count_ bigint,
   5         community_id_ integer)
   6     LANGUAGE plpgsql
   7     AS $$
   8 BEGIN
   9     RETURN query
  10     SELECT
  11         count(*),
  12         community_id
  13     FROM (
  14         SELECT
  15             c.creator_id,
  16             p.community_id
  17         FROM
  18             comment c
  19             INNER JOIN post p ON c.post_id = p.id
  20             INNER JOIN person pe ON c.creator_id = pe.id
  21         WHERE
  22             c.published > ('now'::timestamp - i::interval)
  23             AND pe.bot_account = FALSE
  24         UNION
  25         SELECT
  26             p.creator_id,
  27             p.community_id
  28         FROM
  29             post p
  30             INNER JOIN person pe ON p.creator_id = pe.id
  31         WHERE
  32             p.published > ('now'::timestamp - i::interval)
  33             AND pe.bot_account = FALSE) a
  34 GROUP BY
  35     community_id;
  36 END;
  37 $$;
  38
  39 CREATE OR REPLACE FUNCTION site_aggregates_activity (i text)
  40     RETURNS integer
  41     LANGUAGE plpgsql
  42     AS $$
  43 DECLARE
  44     count_ integer;
  45 BEGIN
  46     SELECT
  47         count(*) INTO count_
  48     FROM (
  49         SELECT
  50             c.creator_id
  51         FROM
  52             comment c
  53             INNER JOIN person u ON c.creator_id = u.id
  54             INNER JOIN person pe ON c.creator_id = pe.id
  55         WHERE
  56             c.published > ('now'::timestamp - i::interval)
  57             AND u.local = TRUE
  58             AND pe.bot_account = FALSE
  59         UNION
  60         SELECT
  61             p.creator_id
  62         FROM
  63             post p
  64             INNER JOIN person u ON p.creator_id = u.id
  65             INNER JOIN person pe ON p.creator_id = pe.id
  66         WHERE
  67             p.published > ('now'::timestamp - i::interval)
  68             AND u.local = TRUE
  69             AND pe.bot_account = FALSE) a;
  70     RETURN count_;
  71 END;
  72 $$;
  73