title: Count the number of total newsletter subscribers per month
body: <p>I'm banging my head for several hours now, trying to figure out how I could <strong>count the number of total newsletter subscribers per month</strong> using Redshift.</p>
  
  <p>Base for the calculation is a events table that tracks every user action, specifically wether he <em>subscribes</em> or <em>unsubscribes</em> from the newsletter. Simplified it looks like this:</p>
  
  <pre><code>+----------------------+---------+---------------+
  | timestamp            | user_id | action        |
  +----------------------+---------+---------------+
  | 2017-01-01T12:10:31Z |       1 | subscribed    |
  | 2017-01-01T13:11:51Z |       2 | subscribed    |
  | 2017-01-01T13:15:53Z |       3 | subscribed    |
  | ...                  | ...     | ...           |
  | 2017-02-17T09:42:33Z |       4 | subscribed    |
  | ...                  | ...     | ...           |
  | 2017-03-15T16:59:13Z |       1 | unsubscribed  |
  | 2017-03-17T02:19:56Z |       2 | unsubscribed  |
  | 2017-03-17T05:33:05Z |       2 | subscribed    |
  | ...                  | ...     | ...           |
  </code></pre>
  
  <p>For every month I like to sum up the number of users that <em>subscribed</em> to the newsletter plus the number of users that <em>were already subscribed</em> and didn't unsubscribe. In the example above we would have 3 users in January, another one is added in February for a total of 4 subscribers. Then in March we lose one user while another one only unsubscribes temporarily. The number of total subscribers we have in March is 3. </p>
  
  <p>The end result I'm looking for is something like this:</p>
  
  <pre><code>+------------+-------------+
  | month      | subscribers |
  +------------+-------------+
  | 2017-01-01 |           3 |
  | 2017-02-01 |           4 |
  | 2017-03-01 |           3 |
  | ...        |         ... |
  </code></pre>
  
  <p>Any ideas if and how this would be solvable using an SQL query (preferably working in Redshift or Postgres)?</p>