Sum for a rolling total

Question

I have the following query:

    select b.month_date,total_signups,active_users from
    (
    SELECT date_trunc('month',confirmed_at) as month_date
    , count(distinct id) as total_signups
       FROM follower.users
       WHERE confirmed_at::date >= dateadd(day,-90,getdate())::date
       and (deleted_at is  null or deleted_at > date_trunc('month',confirmed_at))
      group by 1
    ) a ,
 
      (
      SELECT date_trunc('month', inv.created_at) AS month_date
  ,COUNT(DISTINCT em.user_id) AS active_users
  FROM follower.invitees inv
  INNER JOIN follower.events
   ON inv.event_id = em.event_id
  where inv.created_at::date >= dateadd(day,-90,getdate())::date
  GROUP BY 1
    ) b
    where a.month_date=b.month_date

This returns three columns month date, total signups and active users, what I need is a rolling total for all users in the fourth column (rolling total of signups). I've tried over and partition functions with no luck. Could someone help? Appreciate it very much.

Answer 1

Try adding this column definition to your first Select:

SUM(total_signups) 
     OVER (ORDER BY b.month_date ASC rows between unbounded preceding and current row) 
     AS running_total

Here's a mini-demo