title: Postgresql running totals with groups missing data and outer joins
body: <p>I've written a sql query that pulls data from a user table and produces a running total and cumulative total of when users were created. The data is grouped by week  (using the windowing feature of postgres). I'm using a left outer join to include the weeks when no users where created. Here is the query...</p>
  
  <pre><code>&lt;!-- language: lang-sql --&gt;
  
  WITH reporting_period AS (
     SELECT generate_series(date_trunc('week', date '2015-04-02'), date_trunc('week', date '2015-10-02'), interval '1 week') AS interval
  )
  
  SELECT 
    date(interval) AS interval
  , count(users.created_at) as interval_count 
  , sum(count( users.created_at) ) OVER (order by date_trunc('week', users.created_at)) AS cumulative_count 
  
   FROM reporting_period 
   LEFT JOIN users 
   ON interval=date(date_trunc('week', users.created_at) )
  
  GROUP BY interval, date_trunc('week', users.created_at) ORDER BY interval
  </code></pre>
  
  <p>It works almost perfectly. The cumulative value is calculated properly for weeks week a user was created. For weeks when no user was create it is set to grand total and not the cumulative total up to that point. </p>
  
  <p>Notice the rows with ** the Week Tot column (interval_count) is 0 as expected but the Run Tot (cumulative_total) is 1053 which equals the grand total. </p>
  
  <pre><code>    Week          Week Tot   Run Tot
  -----------------------------------
  2015-03-30        | 4        | 4
  2015-04-06        | 13       | 17
  2015-04-13        | 0        | 1053 **
  2015-04-20        | 9        | 26
  2015-04-27        | 3        | 29
  2015-05-04        | 0        | 1053 **
  2015-05-11        | 0        | 1053 **
  2015-05-18        | 1        | 30
  2015-05-25        | 0        | 1053 **
  ...
  2015-06-08        | 996      | 1031
  ...
  2015-09-07        | 2        | 1052
  2015-09-14        | 0        | 1053 **
  2015-09-21        | 1        | 1053 **
  2015-09-28        | 0        | 1053 **
  </code></pre>
  
  <p>This is what I would like</p>
  
  <pre><code> Week              Week Tot   Run Tot
  -----------------------------------
  2015-03-30        | 4        | 4
  2015-04-06        | 13       | 17
  2015-04-13        | 0        | 17 **
  2015-04-20        | 9        | 26
  2015-04-27        | 3        | 29
  2015-05-04        | 0        | 29 **
  ...
  </code></pre>
  
  <p>It seems to me that if the outer join can somehow apply the grand total to the last column it should be possible to apply the current running total but I'm at a loss on how to do it.</p>
  
  <p>Is this possible?</p>