Help on an (another) SQL query

Question

Following my previous 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

I have a query that returns how many new users I have for a given date and timespan:

 SET @till_date='2009-11-01'; #for today, give CURDATE()+1
 SET @duration=1; # this can be changed to 7, 31, etc
 SELECT COUNT(guid) AS total_new_users, DATE_SUB(@till_date, INTERVAL @duration DAY) AS since_date, @till_date AS till_date
 FROM `activity` WHERE 1
  AND `when` BETWEEN DATE_SUB(@till_date, INTERVAL @duration DAY) AND @till_date
  AND guid NOT IN
  (
   SELECT guid
   FROM `activity`
   WHERE `when` < DATE_SUB(@till_date, INTERVAL @duration DAY)
  )

I want to be able to build a query that will return a list of new users per date, for a given time span. It will be used to build a graph of new users per day for a month, week, etc.

Something like this:

total_new_users  |  since_date  |  till_date
----------------------------------------------
   10            |  2009-11-20  |  2009-11-21
   10            |  2009-11-21  |  2009-11-22
   10            |  2009-11-22  |  2009-11-23

I thought about using UNION with LOOP, but my SQL knowledge is pretty basic, help?

The result (thanks to @pilcrow):

SET @till_date='2009-11-15';
SET @since_date='2009-11-01';
SELECT first AS yyyymmdd, 
   COUNT('x') AS new_user_count
FROM (SELECT guid,
             DATE(MIN(`when`)) AS first
      FROM activity
      GROUP BY 1) first_appearance
WHERE first BETWEEN @since_date AND @till_date
GROUP BY 1

@Suriit: yea, was in my previous question, added here as well. — Amirshk
– Amirshk, Commented Nov 24, 2009 at 3:54

pilcrow · Accepted Answer · 2009-11-24 04:26:50Z

2

Presuming SET SESSION sql_mode = 'ANSI_QUOTES':

SELECT first AS yyyymmdd, 
       COUNT('x') AS new_user_count
FROM (SELECT guid,
             MIN("when") AS first
      FROM activity
      GROUP BY 1) first_appearance
WHERE first BETWEEN @since_date AND @till_date
GROUP BY 1

You may of course substitute your DATE_SUB() for @since_date, if you like.

answered Nov 24, 2009 at 4:26

pilcrow

59.2k14 gold badges101 silver badges147 bronze badges

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

Comments

Suroot · Accepted Answer · 2009-11-24 04:10:14Z

0

SELECT 
    COUNT(DISTINCT guid) AS total_new_users, 
    DATE(when) AS since_date, 
    DATE(DATE_ADD(when, INTERVAL 1 DAY)) AS till_date 
FROM 
    Activity 
GROUP BY 
    DATE(when)

edited Nov 24, 2009 at 4:10

answered Nov 24, 2009 at 4:04

Suroot

4,4531 gold badge26 silver badges28 bronze badges

2 Comments

Amirshk Over a year ago

@Suroot: your query returns the unique guids per day. but a "new user" is identified by showing up in that day, and not in any of the previous days. this is why i have the complicated query in my question.

pilcrow Over a year ago

@Suroot, see Am's linked question -- the dataset is more clearly articulated there.

Collectives™ on Stack Overflow

Help on an (another) SQL query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related