CODEWITHSUNDEEP
mysqlaggregate
Charlie Skilbeck
Charlie Skilbeck

Reputation: 1141

MySQL query - find "new" users per day

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.

Upvotes: 6

Views: 2683

Answers (6)

sumit kumar singh
sumit kumar singh

Reputation: 1

select EventTimeStamp , sum(cnt) from 
(
    with usr_min as 
    (  
        select UserID, min(EventTimeStamp) as min_EventTimeStamp from table group by UserID 
    )
    select a.UserID, a.EventTimeStamp,
           CASE when a.EventTimeStamp = b.min_EventTimeStamp then 1
           ELSE 0 END AS cnt
    from table a join usr_min b ON a.UserID = b.UserID
)
group by EventTimeStamp

Upvotes: 0

Charlie Skilbeck
Charlie Skilbeck

Reputation: 1141

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)

Upvotes: 2

Konerak
Konerak

Reputation: 39763

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)

Upvotes: 1

Webist
Webist

Reputation: 37

86400-(EventTimeStamp) as new_users

Upvotes: -2

Gareth
Gareth

Reputation: 21

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

Upvotes: 2

Jason S
Jason S

Reputation: 189646

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).

Upvotes: 1

Related Questions