SQL query for active users

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

SELECT COUNT(*) 
FROM tab开发者_Python百科le.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

Replace COUNT(*) with *.

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)

If each data row in your table represents a distinct user, you can use the query below.

SELECT COUNT(1), date
FROM table
WHERE date >= (now() - interval '30 days')
GROUP BY 2 ORDER BY 2 DESC;

If your table includes all sessions, but it has a distinct id per user, then you can use the query below.

SELECT COUNT(DISTINCT distinct_id), date
FROM table
WHERE date >= (now() - interval '30 days')
GROUP BY 2 ORDER BY 2 DESC;