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

   1 -- Add monthly and half yearly active columns for site and community aggregates
   2
   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;
   8
   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;
  13
  14 create or replace function site_aggregates_activity(i text)
  15 returns int
  16 language plpgsql
  17 as
  18 $$
  19 declare
  20    count_ integer;
  21 begin
  22   select count(*)
  23   into count_
  24   from (
  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)
  28     and u.local = true
  29     union
  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)
  33     and u.local = true
  34   ) a;
  35   return count_;
  36 end;
  37 $$;
  38
  39 update site_aggregates
  40 set users_active_day = (select * from site_aggregates_activity('1 day'));
  41
  42 update site_aggregates
  43 set users_active_week = (select * from site_aggregates_activity('1 week'));
  44
  45 update site_aggregates
  46 set users_active_month = (select * from site_aggregates_activity('1 month'));
  47
  48 update site_aggregates
  49 set users_active_half_year = (select * from site_aggregates_activity('6 months'));
  50
  51 create or replace function community_aggregates_activity(i text)
  52 returns table(count_ bigint, community_id_ integer)
  53 language plpgsql
  54 as
  55 $$
  56 begin
  57   return query
  58   select count(*), community_id
  59   from (
  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)
  63     union
  64     select p.creator_id, p.community_id from post p
  65     where p.published > ('now'::timestamp - i::interval)
  66   ) a
  67   group by community_id;
  68 end;
  69 $$;
  70
  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_;
  75
  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_;
  80
  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_;
  85
  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_;