title: SQL query to conditionally sum based on moving date window
body: <p>I'm trying to figure out some sliding window stats on my users.  I have a table with a user, and columns such as created_at and verified_at.  For each month, I'd like to find out how many users registered (a simple group by date_trunc of the created_at), and then of those people, how many verified within my sliding window (call it 60 days).</p>
  
  <p>I'd like to do a SQL query that gives me something like:</p>
  
  <pre><code>Month    | Registered | Verified in 60 days
  Jan 2009 | 1543       | 107
  Feb 2009 | 2000       | 250
  </code></pre>
  
  <p>I'm using postgresql.  I starting looking at sum(case...), but I don't know if I can get my case to be dependent on the date_trunc somehow.</p>
  
  <p>This doesn't work, of course, but here's the idea:</p>
  
  <pre><code>SELECT DATE_TRUNC('month', created_at) as month, 
  COUNT(*) as registered,
  SUM(CASE WHEN verified_at &lt; month+60 THEN 1 ELSE 0 END) as verified
  FROM users
  GROUP BY DATE_TRUNC('month', created_at)
  </code></pre>