title: MySQL query to retrieve DISTINCT COUNT between moving DATE period
body: <p>I'm trying to write a query that returns a list of dates and the DISTINCT COUNT of User IDs for the 7 days preceding each date. The table I'm working with is simple, and looks like this:</p>
  
  <pre><code>Started                 UserId
  "2012-09-25 00:01:04"   164382
  "2012-09-25 00:01:39"   164382
  "2012-09-25 00:02:37"   166121
  "2012-09-25 00:03:35"   155682
  "2012-09-25 00:04:18"   160947
  "2012-09-25 00:08:19"   165806
  </code></pre>
  
  <p>I can write the query for output of an individual COUNT as follows:</p>
  
  <pre><code>SELECT COUNT(DISTINCT UserId)
  FROM Session 
  WHERE Started BETWEEN '2012-09-18 00:00' AND '2012-09-25 00:00';
  </code></pre>
  
  <p>But what I'm trying to do is output this COUNT for every day in the table AND the 7 days preceding it. To clarify, the value for September 25th would be the count of DISTINCT User IDs between the 18th and 25th, the 24th the count between 17th and 24th, etc.</p>
  
  <p>I tried the following query but it provides just the COUNT for each day:</p>
  
  <pre><code>SELECT 
  DATE(A.Started),
  Count(DISTINCT A.UserId)
  FROM Session AS A
  WHERE DATE(A.Started) BETWEEN DATE(DATE_SUB(DATE(DATE(A.Started)),INTERVAL 7 DAY)) AND DATE(DATE(A.Started))
  GROUP BY DATE(A.Started)
  ORDER BY DATE(A.Started);
  </code></pre>
  
  <p>And the output looks like this:</p>
  
  <pre><code>DATE(A.Started) "Count(DISTINCT A.UserId)"
  2012-09-18          709
  2012-09-19          677
  2012-09-20          658
  2012-09-21          556
  2012-09-22          530
  2012-09-23          479
  2012-09-24          528
  2012-09-25          480
  ...
  </code></pre>
  
  <p>But as I said, those are just the daily counts. Initially I thought I could just sum the 7 day values, but that will invalidate the DISTINCT clause. <strong>I need the DISTINCT UserId counts for each 7 day period preceding a given date.</strong></p>