title: Weekly Active Users for each day from log
body: <p>I was wondering if someone could help me with some SQL for returning the amount of unique users logged into a database table during a period of two or more days (let's use 7 days as a reference).</p>
  
  <p>My log table contains a timestamp (ts) and user_id in each row, representing activity from that user at that time.</p>
  
  <p>The following query returns the Daily Active Users or DAU from this log:</p>
  
  <pre><code>SELECT FLOOR(ts / 86400) AS day, COUNT(DISTINCT user_id) AS dau
  FROM log
  GROUP BY day ORDER BY day ASC
  </code></pre>
  
  <p>Now let's say I would like to add to this single query (or at least retrieve in the most efficient possible fashion) the Weekly Active Users, or total unique users logged for a period of 7 days. However, I don't want to divide my time in non-overlapping weeks. What I need is to count, for each day, the distinct user_ids seen during that day and the 6 previous days.</p>
  
  <p>For example:</p>
  
  <pre><code>day users wau
  1   1,2   2
  4   1,3   3
  7   3,4,5 5
  8   5     4    (user_id 2 lost from count)
  15  2     2    (user_ids 1,3,4 lost from count)
  </code></pre>
  
  <p>Thank you for any help you can provide and feel free to ask via comment if you need further clarification.</p>