SQL query for active users

Question

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

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

How can I change it to get not one value, but a table of active users for a date range?

My desired result would look like this:

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

Thank,

Rob

Answer 1

Replace COUNT(*)with *.

Answer 2

I do not know which database you are using, so it is difficult to be specific. If your dates do not have time, 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

, ( , 30- ):

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)

Answer 3

If each row of data in your table represents an individual 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 separate identifier for each user, 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;