SQL query for active users

Question

This SQL query gives me today's number of users active in the last 30 days:

SELECT COUNT(*) 
FROM table.users
WHERE creation_tsz >= (now() - interval '30 days')

How can I modify it to get not a single value, but a table of active users for a range of dates?

My desired output would look like this:

Date       Users active in the last 30 days  
1/1/2010   10000  
1/2/2010   11234  
1/3/2010   12343  
1/4/2010   15944  
...        ... 

Thanks,

Rob

Answer 1

Replace COUNT(*) with *.

Answer 2

I don't know what database you are using so it is hard to be specific. If there are no times in your dates, you can do this:

SELECT creation_tsz as Date, COUNT(*) as Count
FROM table.users
WHERE creation_tsz >= (now() - interval '30 days')
GROUP BY creation_tsz

Otherwise, this will get you pretty close to what you want (year excluded because you are only doing a 30 day range):

SELECT month(creation_tsz) as Month, day(creation_tsz) as Day, COUNT(*) as Count
FROM table.users
WHERE creation_tsz >= (now() - interval '30 days')
GROUP BY month(creation_tsz), day(creation_tsz)