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
Replace COUNT(*) with *.
count(*) with name, lastname, email, .... Or reduce the time window, i.e. interval '5 days'where creation_tsz between ('2010-01-01' - interval '30 days') and '2010-01-01'. Maybe you can post an example of the output you'd like to see.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;
