Databases
StackOverflow2013
Postsdbo
POHelp on an (another) SQL query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHelp on an (another) SQL query
    text
    copied!<p>Following my previous <a href="https://stackoverflow.com/questions/1786216/help-on-an-sql-query">question</a>.</p> <p>I have a table named <code>activity</code> with 2 columns:</p> <pre><code>`when` as datetime // last time i saw a user guid as varchar // a unique identifier for each user </code></pre> <p>I have a query that returns how many new users I have for a given date and timespan:</p> <pre><code> 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` &lt; DATE_SUB(@till_date, INTERVAL @duration DAY) ) </code></pre> <p>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.</p> <p>Something like this:</p> <pre><code>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 </code></pre> <p>I thought about using <code>UNION</code> with <code>LOOP</code>, but my SQL knowledge is pretty basic, help?</p> <hr> <p><strong>The result</strong> (thanks to @pilcrow):</p> <pre><code>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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload