CODEWITHSUNDEEP
sqlmysql
Amirshk
Amirshk

Reputation: 8258

Help on an SQL query

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

Upvotes: 0

Views: 164

Answers (5)

Jim Garrison
Jim Garrison

Reputation: 86774

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.

Upvotes: 2

theShingles
theShingles

Reputation: 365

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.

Upvotes: 1

Jacob Schoen
Jacob Schoen

Reputation: 14212

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.

Upvotes: 0

curtisk
curtisk

Reputation: 20175

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

Upvotes: 4

adamcodes
adamcodes

Reputation: 1606

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.

Upvotes: 0

Related Questions