SQL query to conditionally sum based on moving date window

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).

I'd like to do a SQL query that gives me something like:

Month    | Registered | Verified in 60 days
Jan 2009 | 1543       | 107
Feb 2009 | 2000       | 250
开发者_开发知识库

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.

This doesn't work, of course, but here's the idea:

SELECT DATE_TRUNC('month', created_at) as month, 
COUNT(*) as registered,
SUM(CASE WHEN verified_at < month+60 THEN 1 ELSE 0 END) as verified
FROM users
GROUP BY DATE_TRUNC('month', created_at)

---

SELECT  COUNT(created_at) AS registered,
        SUM(CASE WHEN verified_at <= created_at + '60 day'::INTERVAL THEN 1 ELSE 0 END) AS verified
FROM    generate_series(1, 20) s
LEFT JOIN
        users
ON      created_at >= '2009-01-01'::datetime + (s || ' month')::interval
        AND created_at < '2009-01-01'::datetime + (s + 1 || ' month')::interval
GROUP BY
        s

---

perhaps you could union together the different months.

select sum(whatever), 'january' from user where month = 'january'
union all
select sum(whatever), 'february' from user where month = 'february'
...

---

SELECT
    MONTH,
    COUNT(*) AS Registered,
    SUM (CASE WHEN datediff(day,reg_date,ver_date) < 60 THEN 1 ELSE 0) as 'Verified in 60 //days datediff is an MSSQL function amend for postgresql'
FROM
    TABLE
GROUP BY
    MONTH