title: Aggregate previous rows in Postgres query
body: <p>I have a table storing activity information for my application's users.</p>
  
  <pre><code>| username | day |
  |----------|-----|
  |   u1     |   1 |
  |   u1     |   2 |
  |   u1     |   3 |
  |     u2   |   2 |
  |       u3 |   1 |
  |       u3 |   4 |
  </code></pre>
  
  <p>I'd like to be able to get historical data regarding unique and recent users for each day.</p>
  
  <ul>
  <li>Unique users for day N are all the distinct users that had any activity between day 0 and day N.</li>
  <li>Recent users for day N are all the distinct users that had any activity on day N-1 or day N. In the actual application this will be between day N-30 and N.</li>
  </ul>
  
  <p>I'm able to get the list of the users that were active on each specific day, but I'm not sure how I can aggregate this data to get unique or recent users.</p>
  
  <pre><code>SELECT 
  day, 
  array_agg(username) as day_users
  FROM myTable
  GROUP BY day
  ORDER BY day;
  
  | day | day_users |
  |-----|-----------|
  |   1 |  u1,   u3 |
  |   2 |  u1,u2    |
  |   3 |  u1       |
  |   4 |        u3 |
  </code></pre>
  
  <p>For the sample data above, the expected output would be (spacing not required):</p>
  
  <pre><code>| day | unique_users | recent_users 
  |-----|--------------|-------------
  |   1 |     u1,   u3 |     u1,   u3
  |   2 |     u1,u2,u3 |     u1,u2,u3
  |   3 |     u1,u2,u3 |     u1,u2
  |   4 |     u1,u2,u3 |     u1,   u3
  </code></pre>
  
  <p>Relevant SQL Fiddle: <a href="http://sqlfiddle.com/#!17/b793f/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/b793f/1</a></p>