Help on an SQL query

Question

I have a table named activity with 2 columns:

when as datetime // last time i saw a user
guid as varchar  // a unique identifier for each user

This tables is used to track usage in a certain resource. Guid are created by the clients.

I want to be able to query how many new users I have in the last day/week/month. A new user is identified by a guid appearing in the activity table for the first time.

How can I do this in a single SQL statement?

update: The table contains an entry for every time a user is using the resource. So if he used 5 times, there will be 5 rows. Until a user uses the resource, i don't have his guid and there is no entry in table for him.

The result:

Thank you all, it helped a lot. For anyone interested, this is what I compiled from all your suggestions:

SET @duration=7;
SELECT COUNT(distinct guid) AS total_new_users FROM `activity`
    WHERE `when` >= DATE_SUB(CURDATE(),INTERVAL @duration DAY) AND guid NOT IN
    (
        SELECT guid
        FROM `activity`
        WHERE `when` < DATE_SUB(CURDATE(),INTERVAL @duration DAY)
    );

Do you want a count of newly registered users within the last week, or a count of all the users that have been seen within the last week. — Mark Byers
– Mark Byers, Commented Nov 23, 2009 at 21:48

curtisk · Accepted Answer · 2009-11-23 21:50:36Z

4

select count(guid)as total_new_users
from activity
where when >= {last day/week/month}
and guid not in(select guid
from activity
where when < {last day/week/month})

answered Nov 23, 2009 at 21:50

curtisk

20.2k4 gold badges62 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Garrison · Accepted Answer · 2009-11-23 22:42:14Z

2

I think what you want is:

select count(*) from (
  select guid,min(when) as first from activity group by guid 
  having first between curdate()-interval 7 day and curdate() )

You have to query the entire table because otherwise people will be counted as new for their first visit during the time period in question. This query finds the first time each guid was seen, then filters out those that aren't in the period.

edited Nov 23, 2009 at 22:42

answered Nov 23, 2009 at 21:54

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

5 Comments

curtisk Over a year ago

@Jim: interesting approach! add the table though

Amirshk Over a year ago

I think my MySQL is too old, it cant use the DAYS keyword. (ver 4.1.22).

pilcrow Over a year ago

@Jim: I like this use of HAVING. For +1, please do correct the syntax, adding the table as curtisk asked ("FROM activity") and singularizing the unit of INTERVAL ("7 DAY").

Jim Garrison Over a year ago

@pilcrow (nice handle) I have corrected the sql as you suggested

Amirshk Over a year ago

I cant upvote this for some reason, +1 just for showing me the having concept.

theShingles · Accepted Answer · 2009-11-23 22:34:32Z

1

SELECT COUNT(DISTINCT guid) 
FROM activity
WHERE guid NOT IN (
    SELECT DISTINCT guid FROM activity WHERE when < CURDATE() - INTERVAL 7 DAYS
)
AND when BETWEEN CURDATE() AND CURDATE() - INTERVAL 7 DAYS;

change the 7 days to how ever long.

edited Nov 23, 2009 at 22:34

answered Nov 23, 2009 at 21:48

theShingles

3651 gold badge4 silver badges11 bronze badges

Comments

adamcodes · Accepted Answer · 2009-11-23 21:47:18Z

0

Need claraification. If when is not populated on creation, then just SELECT * FROM activity WHERE when IS null. But I'm not sure if that's what you mean.

answered Nov 23, 2009 at 21:47

adamcodes

1,60613 silver badges21 bronze badges

Comments

Jacob Schoen · Accepted Answer · 2009-11-23 21:53:13Z

0

SELECT COUNT(DISTINCT guid) 
FROM ACTIVITY 
WHERE when BETWEEN CURDATE() AND CURDATE() - INTERVAL 7 DAYS
  AND guid NOT IN (SELECT distinct GUID FROM ACTIVITY WHERE when < (CURDATE() -7 DAYS))

That should do it.

answered Nov 23, 2009 at 21:53

Jacob Schoen

14.2k15 gold badges87 silver badges107 bronze badges

Collectives™ on Stack Overflow

Help on an SQL query

5 Answers 5

Comments

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related