title: Cumulative open subscriptions with start_date and end_date on Redshift
body: <p>I am trying to write a query that will allow to me to count the number of active subscriptions by day in Redshift.</p>
  
  <p>I have the following table:</p>
  
  <pre><code>  sub_id   | start_date |  end_date
    ---------------------------------------
     20001   | 2017-09-01 |   NULL
     20002   | 2017-08-01 | 2017-08-29
     20003   | 2016-01-01 | 2017-04-25
     20004   | 2016-07-01 | 2017-09-03
  </code></pre>
  
  <p>I would like to be able to state, for each date between two dates how many subscriptions are active, such that:</p>
  
  <pre><code>      date    | active_subs
    ------------------------
    2016-06-30  |  1
    2016-07-01  |  2
      ...       |
    2017-04-24  |  2
    2017-04-25  |  1
      ...       |
    2017-07-31  |  1
    2017-08-01  |  2
      ...       |
    2017-08-28  |  2
    2017-08-29  |  1
    2017-08-30  |  1
    2017-08-31  |  1
    2017-09-01  |  2
    2017-09-02  |  2
    2017-09-03  |  1
  </code></pre>
  
  <p>I have a reference table from which a query can draw 1 row per day with the table name of date and the relevant column being date.ref_date (in the YYYY-MM-DD format)</p>
  
  <p>Do i write this query using window functions or is there a better way?</p>
  
  <p>Thanks</p>