KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
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)
Tags (comma-separated)
Save Edits
Cancel