Using SQL to compute average daily unique usage

Question

I have a MySQL table "stats", which is a list of entries for each login into a website. Each entry has a "userId" string, a "loginTime" timestamp and other fields. There can be more than one entry for each user - one for each login that he makes. I want to write a query that will calculate the average of unique daily logins over, say, 30 days. Any ideas?

Answer 1

i'm probably wrong but if you did: select count(distinct userid) from stats where logintime between start of :day and end of day for day in each of those 30 days fetched those 30 counts (which could be pre-calculated cached (as you probably don't have users logging in at past times)) and them just average them in the programing language that your executing the query from

i read http://unganisha.org/home/pages/Generating_Sequences_With_SQL/index.html while looking and thought if you had a table of say the numbers 0 to 30 lets name it offsets for this example:

select avg(userstoday)
from (select count(userid) as userstoday, day
      from stats join offsets on (stats.logintime=(current_day)-offsets.day)
      group by day)

and as i noted, the userstoday value could be pre-calculated and stored in a table

Answer 2

Thanks everyone, eventually I used: SELECT SUM( uniqueUsers ) / 30 AS DAU FROM (

SELECT DATE( loginTime ) AS DATE, COUNT( DISTINCT userID ) AS uniqueUsers FROM user_requests WHERE DATE( loginTime ) > DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) GROUP BY DATE( loginTime ) ) AS daily_users

I use a SUM and divide by 30 instead of average, because on some days I may not have any logins and I want to account for that. But on any daily heavy-traffic website simply using AVG will give the same results

Answer 3

/* This should give you one row for each date and unique visits on that date */

SELECT DATE(loginTime) LoginDate, COUNT(userID) UserCount

FROM stats

WHERE DATE(loginTime) BETWEEN [start date] AND [end date]

GROUP BY DATE(logintime), userID

Note: It will be more helpful if you can provide some sample data with the result you are looking for.