MySQL query - find “new” users per day

Question

I have a table of data with the following fields

EventID        : Int, AutoIncrement, Primary Key
EventType      : Int                             ' Defines what happened
EventTimeStamp : DateTime                        ' When the Event Happened
UserID         : Int                             ' Unique

The query needs to tell me how many events occurred with new UserIDs for each day in the whole set. So, for each day, how many events exist which have a UserID which doesn't exist in any prior day. I've tried lots, and I can get unique users per day, but can't work out how to get 'NEW' users per day.

Answer 1

First get a table b with for each user when he first arrived, then join that table to get all events for that user for that day.

SELECT DATE(a.EventTimeStamp), COUNT(*) FROM table a
JOIN
(SELECT MIN(EventTimeStamp) AS EventTimeStamp, UserID from table group by userID) b
ON a.UserID = b.UserID
AND DATE(a.EventTimeStamp) = DATE(b.EventTimeStamp) 
GROUP BY DATE(a.EventTimeStamp) 

Answer 2

Select count(EventId) from table
where 
UserId 
  not in (select UserId from table where EventTimeStamp < now() - interval 1 day)

Answer 3

Thank you all for your help - I've voted up the assistance. Here's what I did:

I created these 2 views (I needed to end up with a view, and had to create 2 as it seems you can't nest select statements within views).

Sightings:

select min(to_days(`Events`.TimeStamp)) AS Day0,
    `Events`.TimeStamp AS TimeStamp,
    `Events`.UserID AS UserID
from `Events` group by `Events`.UserID order by `Events`.UserID

NewUsers:

select count(distinct Sightings.UserID) AS Count,
    date(Sightings.TimeStamp) AS Date from Sightings
    group by date(Sightings.TimeStamp)

Answer 4

Good question. I don't have an exact solution but here's the approach I've seen before:

Do a SELECT where you compare the EventTimeStamp with MIN(EventTimeStamp) for a given userID, as determined by a nested SELECT statement on the same table to calculate the MIN timestamp for each ID (e.g. GROUP BY UserID).

Answer 5

86400-(EventTimeStamp) as new_users