SQL query to conditionally sum based on moving date window
SELECT COUNT(created_at) AS registered, SUM(CASE WHEN verified_at <= created_at + '60 day'::INTERVAL THEN 1 ELSE 0 END) AS verifiedFROM generate_series(1, 20) sLEFT JOIN usersON created_at >= '2009-01-01'::datetime + (s || ' month')::interval AND created_at < '2009-01-01'::datetime + (s + 1 || ' month')::intervalGROUP BY s
perhaps you could union together the different months.
select sum(whatever), 'january' from user where month = 'january'union allselect 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 TABLEGROUP BY MONTH