SQL query to conditionally sum based on moving date window

case, postgresql, sql

Solution

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

Problem

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

Original source