title: Redshift SQL - Window function trying to count events up to a certain date
body: <p>I am trying to write a single SQL Statement (window function, CTE?) that would let me find out how many TFS tickets were 'open' each week (in the past), say 10 tickets were opened on '08/26/2019', 15 tickets opened in '09/06/2019, etc.
  Basically, I am trying to answer the question:
  "How many tickets are opened weekly for board='ABC'"?</p>
  
  <p>Here is the regular query that let's me find this, however, I do not want to write code to run this on a loop for each week.
  I feel there has to be a single query (with sub-queries) that would let me do this.</p>
  
  <p>An open ticket for this board, means that at a certain point-in-time it wasn't 'Done' or 'Removed'.</p>
  
  <pre><code>SELECT wi.boardName, DATE_TRUNC('week', wi.changedDate::date)::date as week,
  COUNT(distinct wi.id) AS openCount
  FROM tfs.workitem_revisions wi
  JOIN (
      SELECT id, MAX(rev) AS rev
      FROM tfs.workitem_revisions 
      WHERE UPPER(boardName) = UPPER('ABC')
      AND changedDate &lt; '08/19/2019' --&gt; I would have to do this for every week, trying not to
      GROUP BY 1
  ) wi2 ON (wi.id = wi2.id AND wi.rev = wi2.rev)
  WHERE UPPER(wi.boardName) = UPPER('ABC')
  AND UPPER(state) NOT IN (UPPER('Done'), UPPER('Removed'))
  GROUP BY 1;
  </code></pre>
  
  <p>Current Data Set:
  Each TFS ticket can have X amount of revisions (updates).
  The tricky part is that for each ticket I have to limit it's revisions up to the week, in this case I am using each Monday as the start of a week.</p>
  
  <pre><code>Id      Revision State ChangedDate
  23810   1   New 2019-04-04 23:47:19.0
  23810   2   New 2019-04-04 23:47:28.0
  23810   3   New 2019-04-05 09:09:21.0
  23810   4   New 2019-04-05 09:39:26.0
  23810   5   New 2019-04-09 12:31:23.0
  23810   6   New 2019-04-12 06:59:42.0
  23810   7   New 2019-04-12 09:40:28.0
  23810   8   New 2019-04-15 01:07:12.0
  23810   9   New 2019-04-19 12:47:21.0
  23810   10  New 2019-05-01 14:33:59.0
  23810   11  New 2019-05-01 14:51:20.0
  23810   12  New 2019-05-02 10:55:01.0
  23810   13  New 2019-05-02 11:58:22.0
  23810   14  New 2019-05-14 14:59:37.0
  23810   15  Coding  2019-05-14 15:11:38.0
  23810   16  Coding  2019-05-14 15:11:44.0
  23810   17  Coding  2019-05-14 15:14:02.0
  23810   18  Coding  2019-05-14 15:48:20.0
  23810   19  Coding  2019-05-14 15:49:25.0
  23810   20  Code Complete   2019-05-14 15:49:28.0
  23810   21  Code Complete   2019-05-14 16:58:39.0
  23810   22  Code Complete   2019-05-15 02:23:07.0
  23810   23  Code Complete   2019-05-15 04:05:23.0
  23810   24  Code Complete   2019-05-15 04:05:23.0
  23810   25  Deployed    2019-05-15 04:05:23.0
  23810   26  Deployed    2019-05-15 04:31:15.0
  23810   27  Deployed    2019-05-15 05:49:00.0
  23810   28  Deployed    2019-05-15 05:49:00.0
  23810   29  Deployed    2019-05-15 10:32:26.0
  23810   30  Testing 2019-05-15 11:59:06.0
  23810   31  Testing 2019-05-15 11:59:09.0
  23810   32  Testing 2019-05-15 12:12:28.0
  23810   33  Deployed    2019-05-15 15:40:08.0
  23810   34  Deployed    2019-05-15 15:40:08.0
  23810   35  Code Complete   2019-05-15 15:40:12.0
  23810   36  Code Complete   2019-05-15 15:40:13.0
  23810   37  Coding  2019-05-15 15:40:20.0
  23810   38  Coding  2019-05-15 15:40:20.0
  23810   39  Coding  2019-05-15 15:50:05.0
  23810   40  Coding  2019-05-16 10:16:11.0
  23810   41  Coding  2019-05-16 10:22:37.0
  </code></pre>
  
  <p>Desired result:</p>
  
  <pre><code>ABC 2019-08-12  61
  ABC 2019-08-05  6
  ABC 2019-07-29  1
  ABC 2019-07-22  2
  ABC 2019-07-15  2
  ABC 2019-07-08  2
  ABC 2019-07-01  1
  ABC 2019-06-24  2
  ABC 2019-06-17  53
  ABC 2019-06-10  2
  ABC 2019-05-27  1
  </code></pre>