title: Cohort analysis in SQL
body: <p>Looking to do some cohort analysis on a userbase.  We have 2 tables "users" and "sessions", where users and sessions both have a "created_at" field.  I'm looking to formulate a query that yields a 7 by 7 table of numbers (with some blanks) that shows me: a count of users who were created on a particular day who also have a session created y = (0..6 days ago), indicating that he returned on that day.</p>
  
  <pre><code>created_at  d2  d3  d4
  today       *   *   *
  today-1     49  *   *
  today-2     45  30  *
  today-3     47  48  18
  ...
  </code></pre>
  
  <p>In this case, 47 users who were created on today-3 returned on today-2.</p>
  
  <p>Can I perform this in a single MySQL query?  I can perform the queries individually like so, but it'd be really nice to have it all in one query.</p>
  
  <pre><code>SELECT `users`.* FROM `users` INNER JOIN `sessions` ON `sessions`.`user_id` = `users`.`id` WHERE `users`.`os` = 'ios' AND (`sessions`.`updated_at` BETWEEN '2013-01-16 08:00:00' AND '2013-01-17 08:00:00')
  </code></pre>