CODEWITHSUNDEEP
mysql
GaryO
GaryO

Reputation: 6338

mysql: get new users per day, and cumulative total users by day

Let's say I have a mysql table t:

DateTime timestamp;
int userid;

and I want to know how many total users I have over time. I have this:

2012-12-04 102
2012-12-05 101
2012-12-05 102
2012-12-05 103
2012-12-07 101
2012-12-08 104

So the first time 101 is seen is 12/5, the first time 104 is seen is 12/7, and so on. So I'd like this, for total-users-ever-seen by date:

2012-12-04 1
2012-12-05 3
2012-12-07 3
2012-12-08 4

(I don't care if there are extra dates in there with no new entries.)

The closest I've been able to get is the number of new users per day:

select distinct date, count(*) from 
 (select MIN(DATE(timestamp)) date from t group by userid order by date) t1 \
GROUP BY date;

which seems to work; the subquery gives the earliest timestamp for each userid, and the outer query combines by date. But how can I roll that up to get the total ever seen?

Oh yeah, I looked at MySQL query - find "new" users per day but it didn't seem to do what I'm looking for.

Upvotes: 2

Views: 2361

Answers (2)

Strawberry
Strawberry

Reputation: 33935

SELECT x.timestamp
     , COUNT(DISTINCT y.userid)
  FROM t x
  JOIN t y
    ON y.timestamp <= x.timestamp
 GROUP
    BY timestamp;

GaryO, to understand why this works, just rewrite it this way...

SELECT DISTINCT x.timestamp, y.userid
  FROM t x
  JOIN t y
    ON y.timestamp <= x.timestamp
 ORDER
    BY x.timestamp
     , y.userid;

Using sgeddes's sqlfiddle, this gives us the following intermediate result:

+---------------------+--------+
| timestamp           | userid |
+---------------------+--------+
| 2012-12-04 00:00:00 |    102 |
| 2012-12-05 00:00:00 |    101 |
| 2012-12-05 00:00:00 |    102 |
| 2012-12-05 00:00:00 |    103 |
| 2012-12-07 00:00:00 |    101 |
| 2012-12-07 00:00:00 |    102 |
| 2012-12-07 00:00:00 |    103 |
| 2012-12-08 00:00:00 |    101 |
| 2012-12-08 00:00:00 |    102 |
| 2012-12-08 00:00:00 |    103 |
| 2012-12-08 00:00:00 |    104 |
+---------------------+--------+

So in the final query, all we've done is COUNT this result when grouped by date.

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

Assuming I understand your requirements, you are looking to return the date and the count of users that they first appeared? 

SELECT TimeStamp, COUNT(*) 
FROM T JOIN (
   SELECT MIN(TimeStamp) minDate, UserId
   FROM t 
   GROUP BY UserId) T2 ON T.TimeStamp = T2.minDate AnD T.UserId = T2.UserId
GROUP BY T.TimeStamp

And the Fiddle: http://sqlfiddle.com/#!2/c1f5a/9

Good luck.

Upvotes: 1

Related Questions