title: SQL to calculate retention curve
body: <p>We have an event table with users registering and playing our games.</p>
  
  <p>So assume we have three fields</p>
  
  <pre><code>timestamp ts
  int  userId
  int  eventId               (I.e. 1 = Register, 2 = Login)
  </code></pre>
  
  <p>What we want to calculate is the retention ratio from a given day and onward. A user should be considered active if he have played the last week (i.e. 7 days)</p>
  
  <p>E.g. lets say I want to se the retention curve for users registered 2013-08-01</p>
  
  <p>The output table could be something like</p>
  
  <pre><code>Date         Day     Reg 2013-08-01,  Active,    Retention
  2013-08-01   1       24 567           24 567     100%
  2013-08-02   2       24 567           24 567     100%
  2013-08-03   3       24 567           24 567     100%
  2013-08-04   4       24 567           24 567     100%
  2013-08-05   5       24 567           24 567     100%
  2013-08-05   6       24 567           24 567     100%
  2013-08-05   7       24 567           24 567     100%
  2013-08-05   8       24 567           24 125     98.2%
  2013-08-05   9       24 567           24 027     97.8%
  2013-08-05  10       24 567           23 997     97.5%
  2013-08-05  11       24 567           23 200     96.3%
  2013-08-05  12       24 567           22 890     95.3%
  ....
  </code></pre>
  
  <p>My SQL skills are simply to bad! Free beers (Or GT's In Stockholm ...) for anyone who comes up with this SQL!</p>