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

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